Forum Discussion
Count entries on a category omitting duplicates and blank cells
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, and on column "C" the injury.
On another sheet I want to have the number of injured players for every category, so I'm looking for a formula that counts the number of players on a específico category, without counting the duplicates and without crashing on blank spaces.
The only thing I could come up with is this:
{=SUM(IF(FREQUENCY(IF(IS BLANK(injuries!B2:B15)=FALSE(),MATCH(injuries!B2:B15,injuries!B2:B15,0),""),IF(IS BLANK(injuries!B2:B15)=FALSE(),MATCH(injuries!B2:B15,injuries!B2:B15,0),""))>0,1))}
Wich I frankly don't really understand how it works, and only counts the total of injured players, I've tried to nest an IF(A2:B15="sub 9",....) But it didn't work.
Please kind sirs, I'm in need of your knowledge
- 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
9 Replies
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.
Hi,
Based on the example you attached, you can count the number of injured players by counting the number of occurrences for each category!
You can use this formula:
=COUNTIF($A$2:$A$11,E3)
You can also use pivot tables to summarize the data.
Please find the attached file.
- Thanks for your answer Haytham, but I think I might have not expressed myself clearly, the problem with your formula is that you are counting twice Vicente Lopez, in the list players who had more than one injury are listed once for every injury, but I need to count the number of players, so I don't want to count every player more than once.
In other words, the sub 9 category should count 3 players, not 4.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.
