• 505K Members
• 3,129 Online
• 602K Conversations
SOLVED

## Count entries on a category omitting duplicates and blank cells

Occasional Contributor

# 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, 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.

9 Replies
Highlighted

# Re: Count entries on a category omitting duplicates and blank cells

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.

# Re: Count entries on a category omitting duplicates and blank cells

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.

# Re: Count entries on a category omitting duplicates and blank cells

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.

# Re: Count entries on a category omitting duplicates and blank cells

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.

Solution

# Re: Count entries on a category omitting duplicates and blank cells

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

# Re: Count entries on a category omitting duplicates and blank cells

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

# Re: Count entries on a category omitting duplicates and blank cells

@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.

# Re: Count entries on a category omitting duplicates and blank cells

I did find that, but it was greyed and wouldn't let me chek it

# Re: Count entries on a category omitting duplicates and blank cells

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies