CODEWITHSUNDEEP
google-sheets
PaulH
PaulH

Reputation: 7873

SUMIF across many sheets

I have a Google sheets document where several sheets have the same column layout. I'd like to sum column J if column G = "cat".

Essentially, this:

=SUMIF('A'!G3:G,"=cat", 'A'!J3:J) + SUMIF('B'!G3:G,"=cat", 'B'!J3:J) + SUMIF('C'!G3:G,"=cat", 'C'!J3:J)

But, with lots of sheets, that is cumbersome. How can I reduce that to something like this:

=SUMIF(INDIRECT({A,B,C}&"!G3:G"), "=cat", INDIRECT({A,B,C}&"!J3:J"))

Upvotes: 3

Views: 736

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18727

has a solution with INDIRECT array-formula, see it here.

Unfortunately INDIRECT doesn't support iteration over an array in

Syntax {A,B,C}&"!G3:G is not possible.

Workaround

The first step is to simplify the formula to:

=SUM(FILTER({Sheet1!B:B;Sheet2!B:B},{Sheet1!A:A;Sheet2!A:A} = "Cat"))

The hard part is to manually type all sheet names with ranges. I suggest to make a list of sheet names called Sheets_List:

Sheet1
Sheet2

And then use join to produce the proper formula text:

="{'"&JOIN("'!B:B;'",Sheets_List)&"'!B:B}"

The result is text "{'Sheet1'!B:B;'Sheet2'!B:B}", use it to build the formula.

Upvotes: 1

Related Questions