Google Sheets - Trouble with Query and Array

0
votes

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

  • Sheet1 has 2 data fields (month, store name).

  • Sheet2 has 4 data fields (month, store name, fruit, quantity)

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?

1
google-sheets
Please update the permissions on the sheet so anyone with the link can view. – Aaron Ciuffo

1 Answers

0
votes

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