Forum Discussion

Ka_ChunL's avatar
Ka_ChunL
Copper Contributor
May 20, 2026

Counts+unique+filter

Hi, 

I have a problem when I use the following to count the job

formula Is 

=counta(unique(filter($a:$a, ($b:$b=1)*($c:$c=2)))

the formula is correct, however, 

I found an error if no data match all scenarios, the answer should be 0, but, i got “1” now. Could you please help? 
I tried to set data source instead of whole volume but no change 

excel

2 Replies

    • m_tarler's avatar
      m_tarler
      Silver Contributor
      May 21, 2026

      I indepedently came up with the 'alternaitive' solution offered by SergeiBaklan​ :

      =SUM(--NOT(ISERROR(UNIQUE(FILTER(...

      the problem is that if nothing is found then FILTER will return either the "if not found" value or #CALC! but in either case that is a value/row/output and hence 1.  So the above will just count all the non-error results.

      interestingly the AGGREGATE function will not work by itself but WILL work if you use a helper column...

      in the above image you can see using a helper column (formula in H3 and H2 has the formula text) on the left with the aggregate function pointing at that helper column does work, but when both combined into 1 cell (the first 2 options on the right) the #CALC results.  The last option on the right is the final option I came up with that turns out to be the same as what Sergei suggested in the link.