Count Unique Values with Criteria

Question

Count Unique Values with Criteria

Anonymous

Hi, I have a table that shows species names and material. Some species appear more than once for each substrate type. How can I make a formula that counts the number of species on each material without counting the repeating names?

I have species in column D and substrate in column D. This is an example, but it's clear there's 3 species and 5 entries for the material boulder. I tried doing =COUNTIF(C:C,"boulder") but it returns 5 and not 3.

I need a formula that filters through the material types, and counts each unique string.

C	D
Material	Species
sand	shrimp
boulder	crab
boulder	shrimp
sand	crab
boulder	snail
sand	crab
sand	crab
sand	snail
boulder	shrimp
boulder	crab

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Accepted answer

4 additional answers

Answer 1

Hi Juan Vega Uriostegui,

Thank you for posting on Microsoft Community. We are happy to assist you.

According to your description, you can try these workarounds:

Create a column that list all unique material types using this formula: =UNIQUE(C2:C33) (for example you have 32 material data lines, you can change it base on your data). It will generate a list like picture below

In column next to each material enter this formula: =COUNTA(UNIQUE(FILTER($D$2:$D$33, $C$2:$C$33=E2))).

Hope this help. If you think my reply is helpful, could you please use the feedback tool (picture below) to send feedback for us and mark this as answer?

Because other users will also search information in this community. Your valuable vote will definitely also help other users who have similar queries easily to find the correct channel and useful information more quickly.

Greatly Appreciate again for your patience and understanding.

I wish you keep safe and wish you all the best in year 2025 as well

Sincerely,

Nam-D - MSFT | Microsoft Community Support Specialist.

Answer 2

Hi Juan Vega Uriostegui,

Thank for your update.

This formula calculates the "blank" too.

Please try this new formula: =COUNTA(UNIQUE(FILTER($D$2:$D$33, ($C$2:$C$33=E2)*($D$2:$D$33<>""))))

Hope this help. I look forward to your update.

Sincerely,

Nam-D - MSFT | Microsoft Community Support Specialist.

Answer 3

Ashish Mathur 99,580 Volunteer Moderator

Hi,

In cell D1, enter this formula

=GROUPBY(A1:A13,B1:B13,LAMBDA(a,COUNTA(UNIQUE(a))),3,,,B1:B13<>"")

Hope this helps.

Answer 4

Anonymous

Thanks! It does work with my dataset. I do notice that I have some blank spaces in the species list, and that sometimes the materials have no species. Does this affect the formula somehow? Why does it return a 1 when it should return a 0?

Answer 5

Hi,

In addition to counting unique cases, the following formulas also consider cases where there are empty cells in column C or D.

Apply below formula in cell F1.

=LET(unq, UNIQUE(C1:D20), m, TAKE(unq,, 1), s, TAKE(unq,, -1), GROUPBY(m, s, COUNTA, 3, 1,, (m <> "") * (s <> "")))

If you are using an older version of Excel that does not contain the TAKE, GROUPBY, UNIQUE functions, you can create a list of unique Material starting from cell F2 and then in cell G2 you can use below formula:

=SUM(IF(FREQUENCY(IF((ISNUMBER(SEARCH(F2, $C$2:$C$20))) * ISTEXT($D$2:$D$20), MATCH($D$2:$D$20, $D$2:$D$20, 0)), ROW($D$2:$D$20) - ROW($D$2) + 1), 1))

To apply this formula you will need to simultaneously press CTRL + SHIFT + ENTER on the keyboard. Drag this formula down.

HTH

Share via

Count Unique Values with Criteria

4 additional answers