Forum Discussion

Ka_ChunL's avatar
Ka_ChunL
Copper Contributor
May 20, 2026
Solved

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
  • SergeiBaklan's avatar
    SergeiBaklan
    May 22, 2026

    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)
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 22, 2026

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)
  • Ka_ChunL's avatar
    Ka_ChunL
    Copper Contributor
    May 22, 2026

    Thank you so much. I found correct answer now : )