Forum Discussion
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
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)
4 Replies
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)Thank you so much. I found correct answer now : )
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.
