Forum Discussion
Solved
Count entries on a category omitting duplicates and blank cells
Good afternoon, I hope you can help me I'm working on a spreadsheet of injured players from a football club, I have on column "A" the category of the player, on column "B" the name of the players, an...
- Thank you very much, your formula actually only works when there is only one duplicate, because you count it as half a point, when you add another duplicate it starts increasing, but it inspired me to find the actual solution, instead of assigning a value of 0.5 when the countifs is > 1, I assigned a value of 1/countifs, so it always adds to 1, the new formula is:
{=SUM(IF(COUNTIFS($A$2:$A$11,E3,$B$2:$B$11,$B$2:$B$11)>1,1/COUNTIFS($A$2:$A$11,E3,$B$2:$B$11,$B$2:$B$11),COUNTIFS($A$2:$A$11,E3,$B$2:$B$11,$B$2:$B$11)))}
Again, thank you very much
Vicente,
insert a pivot table and add it to the data model.
category in rows and name in values.
Change the value field settings for name to Distinct Count.
Edit:
One more thing.
Blank rows in a data set are considered bat data. Remove the blank rows.
- Thanks, it sounds like a good way to do it, but I can't manage to find the Distinct Count option on excel 2016, somewhere I read that I need to tick the "Add this data to the Data Model" option when creating the pivot Table, but it's not available for me.
Thanks anyway
Vicente Heyermann Vial wrote:
somewhere I read that I need to tick the "Add this data to the Data Model" option when creating the pivot Table, but it's not available for me.You didn't look hard enough.
- I did find that, but it was greyed and wouldn't let me chek it