How to count not empty cells and avoiding duplicates

Copper Contributor

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:

Marc56_1-1708676826086.png

 

Thank you

5 Replies

@Marc56 

In G3:

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

Fill down.

@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 blanksDistinct Count excluding blanks

@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.

count.png

@Hans Vogelaar 

 

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