Forum Discussion
Counts+unique+filter
CONUNTA() counts all values, includes errors which are are also values of special type. Thus result is 1. As variant that could be
=IFNA( ROWS( UNIQUE( FILTER( $A:$A, ($B:$B=1) * ($C:$C=2), NA() ) ) ), 0)
FILTER and COUNTA function returning 1 even when no data is found | Microsoft Community Hub
The accepted solution from this discussion might answer your question.
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.
