SOLVED

# Count unique text value with specific text criteria (For excel 2016 version)

Occasional Contributor

# Count unique text value with specific text criteria (For excel 2016 version)

Hello All,

I have a case to solve the total no. of food type by respective person as shown below, find out the total no. of food type owned by "Tom" & "Jane" without duplicating the food name.

I have tried several combined functions like sumif, countifs, sumproduct, but all doesn't work, May I know if there's any way to achieve the outcome with a single formula (without using pivot table). Many thanks!!!

10 Replies
best response confirmed by Diem_Carpe (Occasional Contributor)
Solution

# Re: Count unique text value with specific text criteria (For excel 2016 version)

In D2 confirmed with Ctrl+Shift+Enter:

=SUM(IF(\$A\$2:\$A\$16=C2,1/COUNTIFS(\$A\$2:\$A\$16,C2,\$B\$2:\$B\$16,\$B\$2:\$B\$16)))

Fill down.

# Re: Count unique text value with specific text criteria (For excel 2016 version)

I have tried the formula in the excel, and the answer returns "1", is there anything i missed?

# Re: Count unique text value with specific text criteria (For excel 2016 version)

You must confirm the formula with Ctrl+Shift+Enter, otherwise it won't work as intended.

# Re: Count unique text value with specific text criteria (For excel 2016 version)

This is of no use whatsoever for the OP but others may be interested in the changes that Excel 365 is bringing to spreadsheets.  First a traditional nested form

``= COUNTA( UNIQUE( FILTER(Food, Name=@DistinctName) ) )``

.  A sequential alternative is

``````= LET(
SelectedFood, FILTER(Food, Name=@DistinctName),
DistinctFood, UNIQUE(SelectedFood),
COUNTA(DistinctFood) )``````

which is more verbose but can be read without reference to the spreadsheet itself.

# Re: Count unique text value with specific text criteria (For excel 2016 version)

Further to my question, May I know if there's any way to modify the function so that it allows the formula to count the blank cell as well or ignore the rows which cannot match column A and Column B.

# Re: Count unique text value with specific text criteria (For excel 2016 version)

You write "it allows the formula to count the blank cell". Do you mean that blank counts as a food type? So for example, the count for Jane would be 3 ("Apple", "Melon", blank)?

# Re: Count unique text value with specific text criteria (For excel 2016 version)

Sorry for the misleading description. the outcome shall skip counting the blank cell, e.g. Jane would be 2 ("Apple" & "Melon", but an error "#DIV/0!" was shown when i use the same formula.

# Re: Count unique text value with specific text criteria (For excel 2016 version)

In D2 confirmed with Ctrl+Shift+Enter:

=SUM(IF((\$A\$2:\$A\$16=C2)*(\$B\$2:\$B\$16<>""),1/COUNTIFS(\$A\$2:\$A\$16,C2,\$B\$2:\$B\$16,\$B\$2:\$B\$16)))

See the attached version.

# Re: Count unique text value with specific text criteria (For excel 2016 version)

It works, many thanks!!!!!