Forum Discussion

Marc56's avatar
Marc56
Copper Contributor
Feb 23, 2024

How to count not empty cells and avoiding duplicates

Hi, I have a problem to count how many different cells per person in a list, either I can count avoiding the duplicates but empty cells are a problem, or count avoiding empty cells and duplicates are a problem. It seems impossible to fuse these both formula in 1.

 

This is how it looks basically, I want to know how many different cities every person has visited:

 

Thank you

  • Marc56 

    =SUM(N(IF(($B$2:$B$14=F2)*NOT(ISBLANK($C$2:$C$14)),MATCH(IF(($B$2:$B$14=F2)*NOT(ISBLANK($C$2:$C$14)),$C$2:$C$14),IF(($B$2:$B$14=F2)*NOT(ISBLANK($C$2:$C$14)),$C$2:$C$14),)=ROW($1:$13))))

     

    An alternative could be this formula if you work with e.g. Excel 2013. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

  • djclements's avatar
    djclements
    Bronze Contributor

    Marc56 Another option with Excel for MS365:

     

    =LET(
        data, UNIQUE($C$3:$D$14),
        incl, (TAKE(data,, 1)=F3)*(TAKE(data,, -1)<>""),
        IF(OR(incl), ROWS(FILTER(data, incl)), 0)
    )

     

    Or, to spill the entire summary using a single cell array formula:

     

    =LET(
        who, UNIQUE(C3:C14),
        data, UNIQUE(C3:D14),
        results, BYROW(who, LAMBDA(r, LET(
            incl, (TAKE(data,, 1)=r)*(TAKE(data,, -1)<>""),
            IF(OR(incl), ROWS(FILTER(data, incl)), 0)))),
        HSTACK(who, results)
    )

     

    Note: COUNTA with FILTER is incapable of returning 0 when no records are found... it will incorrectly return 1. The ROWS function will return #CALC! when no records are found, which can be handled with either the IFERROR function, or with the IF/OR method shown above to make sure at least one record was found before applying FILTER.

     

    Distinct Count excluding blanks

    • Marc56's avatar
      Marc56
      Copper Contributor

      HansVogelaar 

       

      Hi, it looked like it worked first but if I have a new name with no city visited yet (which can happen in my case) then it counts 1 anyway, adding a city will stay on 1 and after it works by counting uniques...
      It seems to count name and after the cities.
      I think this formula is close to what I want just don't know how to correct this little problem.

      • Marc56 

        Like this:

         

        =IF(COUNTIFS($B$3:$B$14, F3, $C$3:$C$14, "<>")=0, 0, COUNTA(UNIQUE(FILTER($C$3:$C$14, ($B$3:$B$14=F3)*($C$3:$C$14<>"")))))

Resources