COUNTIF #VALUE! error

[1411535]

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:

[#2 · Accepted solution]

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