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...
excel
  • 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.