SUMIFs across multiple sheets
 

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

Answer: SUMIFs across multiple sheets
 

Hi kwyjibo,

Yes, you're right that using SUMIF across multiple sheets can be a bit tricky, but there is a more elegant way to do it using the SUMPRODUCT function.

Here's an example formula that should work for you:

In this formula, "SheetList" is a named range that contains a list of all the sheet names you want to include in the calculation. You can create this named range by selecting all the sheet tabs you want to include, right-clicking, and selecting "Rename" to give them all a common prefix (e.g. "DP"), then selecting all those tabs again and creating a named range with the formula

1. =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255)&"DP"&ROW(INDIRECT("1:52" ))

Here's how the formula works:

1. The INDIRECT function is used to create a range reference that includes all the sheets in the SheetList range. The "&" operator is used to concatenate the sheet name with the cell range reference.
2. The SUMIF function is used to search for values in column B that match Total!A3, and sum the corresponding values in column C.
3. The SUMPRODUCT function is used to sum the results of the SUMIF function across all the sheets in the SheetList range.

SUMIFs across multiple sheets
 

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")) )


"kwyjibo jones" wrote:

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

SUMIFs across multiple sheets
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"kwyjibo jones" wrote:

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

SUMIFs across multiple sheets
 

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"),To tal!A3,INDIRECT("'"&list&"'!C3:C52")))


--

HTH,

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


"kwyjibo jones" wrote in message
...
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

SUMIFs across multiple sheets
 

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

On Aug 13, 4:39*pm, Sean Timmons
wrote:
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")) )

"kwyjibo jones" wrote:
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

SUMIFs across multiple sheets
 

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 wrote:
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

On Aug 13, 4:39 pm, Sean Timmons
wrote:
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")) )

"kwyjibo jones" wrote:
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

SUMIFs across multiple sheets
 

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

Regards,
kwyjibo

On Aug 13, 5:50*pm, Glenn wrote:
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 wrote:
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

On Aug 13, 4:39 pm, Sean Timmons
wrote:
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")) )

"kwyjibo jones" wrote:
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

SUMIFs across multiple sheets
 

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.


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

Regards,
kwyjibo

On Aug 13, 5:50 pm, Glenn wrote:
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 wrote:
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
On Aug 13, 4:39 pm, Sean Timmons
wrote:
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")) )
"kwyjibo jones" wrote:
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