Excel help

STrategos said:
p.s do you really need a pivot ?

Countifs and/or sumifs should work just as well...
Click to expand...

yeah so some team managers need to do evaluations and some capture more than 5 evaluations per person . So the pivot will show how many evaluations were captured by the TM in total

but only taking the ones where the person evaluated was 5 times or less
 
Found the solution for the filter - put the Y and N field in the filter area and then filter it there.

OR

=IF(COUNTIF($A$2:A2,[@Who])<=5,[@Who]&COUNTIF($A$2:[@Who],[@Who]),"N")

You can use that formula. That will take the name and add the count number to it until it gets to 5.
But both are actually the same - just one formula looks a little more complex (but still really easy).

View attachment 542131
 
Last edited:
Excel.PNG

Look at the example

so few names and the count results

MRs and Sam have more than 5

a formula should eliminate the extra rows and max it out at 5 instead of manualy deleting the rows to get it to 5
 
KT-B said:
If you find a solution - please let me know.

And there are tons of videos on pivots. They are one of the most useful reporting tools. They replace the need for many of the other formulas. But pivots can be messed with by "well meaning employees/employers" - so sometimes a static block of data with formulas on a dashboard like sheet is safest.
Click to expand...

Someone did it for me some time back but it doesnt use pivots
Drop me your mail address, will send it to you
 
You could just filter for the "N"s and then use the Delete button and Delete Rows.
You may want to use the Find and Select - Visible only depending on how your machine selects the rows. Then Delete Rows.
 
Last edited:
I.am.Sam said:
yeah so some team managers need to do evaluations and some capture more than 5 evaluations per person . So the pivot will show how many evaluations were captured by the TM in total

but only taking the ones where the person evaluated was 5 times or less
Click to expand...

cool you can do it with a countifs as well
 
KT-B said:
lol. Actually it is a little more complex than I thought.

I am actually using the CountIF function to get the no of rows if it is repeated.
=COUNTIF($A$2:$A$13,A2)
But now my brain has slowed down a bit. I think I am doing this the complicated way. I am sure there is an easier way.



I am really hoping we can just ignore all those with more than 5, and not include 5 of the rows
Click to expand...

Try changing =COUNTIF($A$2:$A$13,A2) to =COUNTIF($A$2:A2,A2), this will give unique numbers to each row, then compile pivot with count rows in the filter and select only unique number 5 and less. Hope this help. After this you can double click on the total, right bottom of your pivot to extract the unique data to a new worksheet
 
Xino_sam said:
Try changing =COUNTIF($A$2:$A$13,A2) to =COUNTIF($A$2:A2,A2), this will give unique numbers to each row, then compile pivot with count rows in the filter and select only unique number 5 and less. Hope this help. After this you can double click on the total, right bottom of your pivot to extract the unique data to a new worksheet
Click to expand...

nice easy way to do it ...and then yeah just filter on 1-5s

perfect
 
Xino_sam said:
Try changing =COUNTIF($A$2:$A$13,A2) to =COUNTIF($A$2:A2,A2), this will give unique numbers to each row, then compile pivot with count rows in the filter and select only unique number 5 and less. Hope this help. After this you can double click on the total, right bottom of your pivot to extract the unique data to a new worksheet
Click to expand...

Yes that only came to me this morning :D Sometimes my brain takes a while to get where it is going.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X