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:



Thank you

5 Replies


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:


    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:


    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




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.


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


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