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.

HansVogelaar's avatar
HansVogelaar
MVP
May 14, 2021

Diem_Carpe 

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

Diem_Carpe's avatar
Diem_Carpe
Copper Contributor
May 17, 2021

HansVogelaar PeterBartholomew1 

 

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.

 

 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 18, 2021

    Diem_Carpe 

    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)?

    • Diem_Carpe's avatar
      Diem_Carpe
      Copper Contributor
      May 19, 2021
      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.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 20, 2021

        Diem_Carpe 

        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.

Resources