Forum Discussion
COUNTIFS returns #VALUE!. Each of the criteria works in its countif. Reason: compare span 2 cols.
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.
The range arguments of COUNTIFS must have the same size.
Option 1: add 2 separate COUNTIFS:
=COUNTIFS($B$6:$B$33,"Apple",$D$6:$D$33,"US")+COUNTIFS($C$6:$C$33,"Apple",$D$6:$D$33,"US")
Option 2: use SUM or SUMPRODUCT
=SUMPRODUCT((B6:C33="Apple")*(D6:D33="US"))
The range arguments of COUNTIFS must have the same size.
Option 1: add 2 separate COUNTIFS:
=COUNTIFS($B$6:$B$33,"Apple",$D$6:$D$33,"US")+COUNTIFS($C$6:$C$33,"Apple",$D$6:$D$33,"US")
Option 2: use SUM or SUMPRODUCT
=SUMPRODUCT((B6:C33="Apple")*(D6:D33="US"))
- got it. Tyvm!
Have you read the documentation?
Important: Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.
