How to count unique values with criteria in Excel - Count unique items based on condition

Welcome to our quick tutorial on how to count unique values based on criteria in Excel.

In the example, I want to count unique values in the Items columns where the size is small. I'm using the FILTER function. The first argument is the array, in this case, select B3:B36. The second argument is the include.
Now, you can use the criteria here. Select the size column and use a criteria. I want to find values where the size is equal to small. Press Enter to get the result. Now we have a list that contains duplicate records. To get a unique list, apply the UNIQUE function. The function returns the unique values from a range. Press enter to get the result. The last step is to count values.
When you want to count the number of unique values, not just numbers, then COUNTA is the appropriate choice. Okay, now we have 4 unique values based on this criteria.

Chapters:
00:00 Intro
00:05 How to count unique values based on criteria in Excel

#exceltips #exceltutorial #excel

[MichaelBrown-lwkz]

I was using COUNTIF instead of FILTER and the formula errored out. This is exactly what I was looking for. Thank you.

[AndresPenaAparicio]

oye padrino, what if I want to add more conditions? for example, I want to do it per month?

[mwferg]

Spent hours this morning trying to figure out how to do this without using a pivot table.

[voltairefelgrand]

Function for counting unique values in a column. (B2:B23 is a filler range. Just replace with the range you want. Works in libre office. I have not tried excel)

For texts and numbers:
=SUM(IF(FREQUENCY(IF(LEN(B2:B23)>0, MATCH(B2:B23, B2:B23, 0), ""), IF(LEN(B2:B23)>0, MATCH(B2:B23, B2:B23, 0), ""))>0, 1))

For numbers:
=SUM(IF(FREQUENCY(B2:B23, B2:B23)>0, 1))