Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:

Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!$B$3:$B$52"),Total!$A3,INDIRECT("'"&SheetList&"'!C$3:C$52"))) 
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,696
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
New 2007 Sumifs formula - is there a new multiple criteria vlookup westy Excel Worksheet Functions 2 July 1st 09 06:41 AM
using sumifs with multiple criteria? Celia Excel Discussion (Misc queries) 3 April 8th 09 02:14 AM
Multiple Sumifs Emeryville John Excel Worksheet Functions 1 September 19th 08 10:12 PM
SumifS Multiple Sum Ranges dee Excel Worksheet Functions 2 May 23rd 07 08:42 PM
SumifS Multiple Sum Ranges Harlan Grove[_2_] Excel Worksheet Functions 0 May 23rd 07 08:40 PM


All times are GMT +1. The time now is 09:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"