Forum Discussion
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 PRODUCTS") with spreadsheet data that combines multiple school sales for products. On a second tab ("GRAPH DATA") I am trying to extract how many cookie sales were made from one school only (VES). Because I have 6 columns of products (1st tab "2022 PRODUCTS") to search from, I am guessing that when I add the 7th criteria range looking for "VES" it's too much? I really don't want to create any additional tabs. Here is my current formula with the issue: =COUNTIFS('2022 PRODUCTS'!B2:G144,"COOKIES",'2022 PRODUCTS'!A2:A144,"VES")
2022 PRODUCTS TAB:
GRAPH DATA TAB:
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"))
1 Reply
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"))