Forum Discussion

1411535's avatar
1411535
Copper Contributor
Jun 02, 2022
Solved

COUNTIF #VALUE! error

Hello, I am struggling trying to find this solution all over the web but am not finding what I am looking for. Please bear with me on excel terminology while I try to explain. I have a tab ("2022 PRO...
Formulas and Functions
  • JoeUser2004's avatar
    JoeUser2004
    Jun 02, 2022

    1411535  wrote:

    ``=COUNTIFS('2022 PRODUCTS'!B2:G144,"COOKIES",

    '2022 PRODUCTS'!A2:A144,"VSE")``

     

    The problem is:  you have 6 columns in the first range and only 1 column in the second range.

     

    From the COUNTIFS help page:  ``Each additional range must have the same number of rows and columns as the criteria_range1 argument.``

     

    Alternatively, use SUMPRODUCT as follows:

     

    =SUMPRODUCT(('2022 PRODUCTS'!B2:G144="COOKIES")

    *('2022 PRODUCTS'!A2:A144="VSE"))

JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Jun 02, 2022

1411535  wrote:

``=COUNTIFS('2022 PRODUCTS'!B2:G144,"COOKIES",

'2022 PRODUCTS'!A2:A144,"VSE")``

 

The problem is:  you have 6 columns in the first range and only 1 column in the second range.

 

From the COUNTIFS help page:  ``Each additional range must have the same number of rows and columns as the criteria_range1 argument.``

 

Alternatively, use SUMPRODUCT as follows:

 

=SUMPRODUCT(('2022 PRODUCTS'!B2:G144="COOKIES")

*('2022 PRODUCTS'!A2:A144="VSE"))

Resources