CODEWITHSUNDEEP
google-sheets
user10771490
user10771490

Reputation: 1

Google Sheets - Trouble with Query and Array

I'm struggling with the following and help/guidance would be appreciated. The attached Google Sheet has 3 sheets;

A query on Sheet3 in cell A3 outputs a set of months and store names from Sheet1 which is then used to find quantities of a given fruit from Sheet2 (in this example, it's apples). The results are listed on Sheet3 in columns D and E. I used both Index(Match) and Filter to output the Apple quantities as i tried to figure out my ultimate goal - how to use a single formula, including the query itself, to get to the aggregate apple total in Row 9 (i.e. without needing to do all the index(match) or filter formulas). Said another way, what formula on Sheet3 in cell D9 would run the query against Sheet1, use the results to find the month, store name, fruit, and quantity matches on Sheet2, and total them for a single output cell on Sheet3 D9?

Thoughts?

Upvotes: 0

Views: 79

Answers (1)

Aaron Ciuffo
Aaron Ciuffo

Reputation: 923

You can use a combination of counta(query()) to do this. For A1:A that contains a mixture of fruit (apples, bananas, grapes, star fruit), use:

=counta((query(A1:A, "select A where A like 'apple'")))

Check out this very simple example sheet

Upvotes: 0

Related Questions