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
Hello Vicente,
I've updated the formula to take into account the issue you mentioned as follows:
=SUM(IF(COUNTIFS($A$2:$A$11,E3,$B$2:$B$11,$B$2:$B$11)>1,0.5,COUNTIFS($A$2:$A$11,E3,$B$2:$B$11,$B$2:$B$11)))
But to enter this formula, you have to press Ctrl+Shift+Enter at the same time to force it to deliver the correct result because it's an array formula.
Please find the attached file.
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
{=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