# How to count not empty cells and avoiding duplicates

Copper Contributor

# 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

5 Replies

# Re: How to count not empty cells and avoiding duplicates

In G3:

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

Fill down.

# Re: How to count not empty cells and avoiding duplicates

@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

# Re: How to count not empty cells and avoiding duplicates

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

# Re: How to count not empty cells and avoiding duplicates

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.

# Re: How to count not empty cells and avoiding duplicates

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