Excel SUMIF across multiple sheets

Question

Hi,
I seem a bit stuck,

Sheet names A, B , C and criteria A1:A10, sum range B1:B10

I gather the basic structure for sum if across multiple sheet is ;

SUMPRODUCT( SUMIF( INDIRECT( "A!"&"A1:A10"),"A",INDIRECT( "A!"&"B1:B10") ))

This is just one sheet and works; y=but if over multiple sheets do I need the cell ranges repeaed or can I use a structure similar to just sum ; so ;

=SUM( 'A:C'!B1:B10 )

so how do I concatenate the three sheets ?

I have used the named range method so; A! , B! and C! = RangeOne

SUMPRODUCT( SUMIF( INDIRECT( RangeOne & "A1:A10"),"A",INDIRECT( RangeOne & "B1:B10" ) ))

Richard.

Answer 1

You can use this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"A","B","C"}&"'!A1:A10"),"A",INDIRECT("'"&{"A","B","C"}&"'!B1:B10")))

Answer 2

Here is an alternate way:

=SUM(SUMIF(INDIRECT(ADDRESS(COLUMN(A1:J1),1,,,D1:D3)),"A",INDIRECT(ADDRESS(COLUMN(A1:J1),2,,,D1:D3))))

D1:D3 contain the sheet names. We are checking in the range A1:A10 for the letter A and them summing the corresponding values in column B.

You can shorten this a little to:

=LET(R,COLUMN(A1:J1),S,D1:D3,SUM(SUMIF(INDIRECT(ADDRESS(R,1,,,S)),"A",INDIRECT(ADDRESS(R,2,,,S)))))

Answer 3

On another sheet, enter the sheet names in a range of cells, for example in A1:A3.

You can then use

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A3&"'!A1:A10"),"A",INDIRECT("'"&A1:A3&"'!B1:B10")))

In Excel in Office 2021 and Microsoft 365, SUM will work instead of SUMPRODUCT.

Answer 4

Thanks, but I think from what you have the

"'"&A1:A3&"'!A1:A10" A1 to A3 would contain the sheet names, that is what I want to avoid, I want to put the sheet names in directly, so here if have a named range

containing A! , B! and C1 , the sheet names,

SUMPRODUCT( SUMIF( INDIRECT( RangeOne&"A1:A10"),"A",INDIRECT( RangeOne&"B1:B10") ))

and here I have what you suggested , cells containing A , B, C in A7 to A9,

SUMPRODUCT( SUMIF( INDIRECT( "'"&A7:A9&"'!"&"A1:A10"),"A",INDIRECT( "'"&A7:A9&"'!"&"B1:B10") ))

So I want to put the sheet names in directly.

RD

Answer 5

Thanks, I'll look into this I have never used ADDRESS,

so will have to figure that first.

Richard.