Hi:  I have a COUNTIFS return #VALUE! when each of the 2 criteria would work in a COUNTIF on its own.

So, =COUNTIF($B$6:$C$33,”Apple”)  and =COUNTIF($D$6:$D$33,”US”)  both work on their own each return the count of rows satisfy the condition.

But if I combine the 2 criteria in a =COUNTIFS($B$6:$C$33,”Apple”,$D$6:$D$33,”US”), this will return #VALUE!

I’ve debugged and found is the criteria spanned 2 columns.   If I adjust to only search within 1 column, the countifs works.

So, changing from:

=COUNTIFS($B$6:$C$33,”Apple”,$D$6:$D$33,”US”)

to:

=COUNTIFS($B$6:$B$33,”Apple”,$D$6:$D$33,”US”)

now works.  So, I need to combine my Col B and Col C into a consolidated Column.

Wanted to check, is this by design?   thnx –LC

I have MS 365 Apps for enterprise.

​Hi:  I have a COUNTIFS return #VALUE! when each of the 2 criteria would work in a COUNTIF on its own.So, =COUNTIF($B$6:$C$33,”Apple”)  and =COUNTIF($D$6:$D$33,”US”)  both work on their own each return the count of rows satisfy the condition.But if I combine the 2 criteria in a =COUNTIFS($B$6:$C$33,”Apple”,$D$6:$D$33,”US”), this will return #VALUE! I’ve debugged and found is the criteria spanned 2 columns.   If I adjust to only search within 1 column, the countifs works.So, changing from:=COUNTIFS($B$6:$C$33,”Apple”,$D$6:$D$33,”US”)to:=COUNTIFS($B$6:$B$33,”Apple”,$D$6:$D$33,”US”)now works.  So, I need to combine my Col B and Col C into a consolidated Column. Wanted to check, is this by design?   thnx –LCI have MS 365 Apps for enterprise.  Read More

​