SOLVED

# COUNTIFS returns #VALUE!. Each of the criteria works in its countif. Reason: compare span 2 cols.

Copper Contributor

# 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.

3 Replies
best response confirmed by lyc999 (Copper Contributor)
Solution

# Re: COUNTIFS returns #VALUE!. Each of the criteria works in its countif. Reason: compare span 2 co

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"))

# Re: COUNTIFS returns #VALUE!. Each of the criteria works in its countif. Reason: compare span 2 co

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.

# Re: COUNTIFS returns #VALUE!. Each of the criteria works in its countif. Reason: compare span 2 co

got it. Tyvm!
1 best response

Accepted Solutions
best response confirmed by lyc999 (Copper Contributor)
Solution

# Re: COUNTIFS returns #VALUE!. Each of the criteria works in its countif. Reason: compare span 2 co

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"))