SUMIFs across multiple sheets

[kwyjibo jones]

Take a look at the following formula

=SUMIF('DP180'!$B$3:$B$52,Total!$A3, 'DP180'!C$3:C$52)

Here, I am searching down the B column in sheet DP180 for values
matching Total!A3. I then sum the corresponding numbers in the C
column.

I would like to do this across multiple sheets - however, the only way
I can do so, is

=SUM(SUMIF('DP180'!$B$3:$B$52,Total!$A3, 'DP180'!C$3:C$52),SUMIF
('DP181.....))

There must be a more elegant way to do this. I've tried putting a
range of sheets into the SUMIF, but that doesn't work. Quick googling
seems to suggest SUMPRODUCT, but I'm not sure how that works, and even
copying simple examples to test SUMPRODUCT results in errors.

Regards,
kwyjibo

 

[Sean Timmons]

From Bob Phillips post:

Put the sheet names in M1:M20 and use

=SUMPRODUCT(SUMIF(INDIRECT($M$1:$M$20&"!$B$3:$B$52"),Total!$A3,INDIRECT($M$1:$M$20&"!C$3:C$52")))

 

[Luke M]

Only certain functions support 3D references. The easiest way may be to
select all the sheets you want to include in reference (using Shift or Ctrl)
and then inputting the SUMIF formula into a cell (note that this will create
the same formula in the same cell on each sheet). Be sure that they all still
reference Total!$A3 as the criteria. You can then use a 3D SUM formula to
capture all those SUMIFs.

 

[RagDyeR]

The first thing you have to do is create a list of your sheet names.

Say you use an out-of-the-way location, maybe Z1 to Z10.

Make sure this list matches *exactly* with the names on the sheet tabs.

Then, try this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z10&"'!B3:B52"),Total!A3,INDIRECT("'"&Z1:Z10&"'!C3:C52")))

If you intend to *add* sheets as you go, you can name the range of sheets in
Z1 to Z10, and expand that named range,
therefore not having to revise the formula itself.

Say you named the range "list",
then try this formula:


=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!B3:B52"),Total!A3,INDIRECT("'"&list&"'!C3:C52")))


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Take a look at the following formula

=SUMIF('DP180'!$B$3:$B$52,Total!$A3, 'DP180'!C$3:C$52)

Here, I am searching down the B column in sheet DP180 for values
matching Total!A3. I then sum the corresponding numbers in the C
column.

I would like to do this across multiple sheets - however, the only way
I can do so, is

=SUM(SUMIF('DP180'!$B$3:$B$52,Total!$A3, 'DP180'!C$3:C$52),SUMIF
('DP181.....))

There must be a more elegant way to do this. I've tried putting a
range of sheets into the SUMIF, but that doesn't work. Quick googling
seems to suggest SUMPRODUCT, but I'm not sure how that works, and even
copying simple examples to test SUMPRODUCT results in errors.

Regards,
kwyjibo

 

[kwyjibo jones]

Thanks for all the input, it's much appreciated. However, I'm still
not there yet.

SeanTimmons response works-
=SUMPRODUCT(SUMIF(INDIRECT($M$1:$M$20&"!$B$3:$B$52"),Total!$A3,INDIRECT
($M$1:$M$20&"!C$3:C$52")))

However, it is not quite there yet.

If you see from the last part of the function above, "!C$3:C$52",
there is no $ in front of the C. When I replicate the formula across
the row, I would like the C column to change, but the B column above
to remain constant.

I cannot seem to do this. Is it possible to take the "!C$3:C$52" bit
outside of the INDIRECT function and still have it work?

Regards,
kwyjibo

 

[Glenn]

See if this works:

=SUMPRODUCT(SUMIF(INDIRECT($M$1:$M$20&"!$B$3:$B$52"),Total!$A3,
INDIRECT($M$1:$M$20&"!R3C"&COLUMN()&":R52C"&COLUMN(),0)))

 

[kwyjibo jones]

That works, thanks. Why do you need a ,0 at the end of the formula?

Regards,
kwyjibo

 

[Glenn]

From the help file under INDIRECT:

Syntax

INDIRECT(ref_text,a1)

A1 is a logical value that specifies what type of reference is contained in
the cell ref_text.

If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.

If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.


In this instance, FALSE and 0 have the same effect.