3

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"))
PaulH
  • 7,759
  • 8
  • 66
  • 143

1 Answers1

1

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.

Community
  • 1
  • 1
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • `=FILTER({Sheet1!B:B},{Sheet1!A:A} = "Cat")` works fine for any single sheet, but when I add a second sheet, I get an `#N/A` error `No matches are found in the filter evaluation`. – PaulH Dec 19 '17 at 18:35