I'm attempting to sum unknown quantities of data that is contained in several sheets. The amount of sheets isn't known either. Thus far, I've simply been using the indirect command on a column with an ever-growing list of sheet names, and that's been working fine with referencing them individually:
A
1 Sheet Name 1
2 Sheet Name 2
3 Sheet Name 3
4 Sheet Name 4
Each of these sheets is formatted identically but contain different data. So one may look like:
A B
1 Apples 7
2 Oranges 8
3 Bananas 3
4 Grapes 5
5 Plums 1
6 Strawberries 8
7 Bananas 3
8 Grapes 5
What I need to do is iterate through my list of sheet names, run a countif
on that data, and then sum all of the results from the various sheets together.
I've tried using:
=sum(arrayformula(countif(indirect(A:A&"!A:B"),"Bananas")))
However, that only returns the result for the first sheet that's named. So in this case I would only get a 2 back, while sheets 2-4 may each contain 10+ Bananas.