Forum Discussion

Diem_Carpe's avatar
Diem_Carpe
Copper Contributor
May 14, 2021
Solved

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. ...
excel
  • HansVogelaar's avatar
    HansVogelaar
    May 14, 2021

    Diem_Carpe 

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 14, 2021

Diem_Carpe 

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.

Resources