SUM function on multiple worksheet

[Rover]

I am hoping some can help me...

Lets' say you have 30 sheets (tabs) in a workbook, and they are all the
formatted the same. There is also a summary sheet that calculates then totals
from each sheet.

To sum the data from all the sheets, I use =(SUM('Sheet 1:Sheet 30'!$C$9)),
and it works fine.

However, how can I enhance the formula to add the cells where there are
pull-down menus (validations).

On the summary sheet I need the totals for X, Y and Z from cell C9 based on
what is selected from the pull down menu in cell C9.

I appreciate the help, I spent lots of time on this today, and my brain hurts.

Regards,
Fred

 

[Ashish Mathur]

Hi,

You essentially want to do a SUMIF() across sheets which is not possible.
You can try the following:

1. Select cell D9 of sheet 1 and then while holding down the Shift key click
on sheet 30. This will group sheet 1 to sheet 30;
2. Now enter the formula =IF(B9=Summary!$B$9,Sheet1!C9,0);
3. Now while the sheets are grouped, delete sheet1! from the above formula;
4. Now in cell C9 of the summary sheet, you can use the formula
=SUM(Sheet1:Sheet30!D9)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Rover]

Ashish,

Thank you for the time to answer my question...It did not work..

But trying to add from mulitple sheet which are identical, but the sheets
have pull down menus (validations). What formulare can I use on the summary
page to sum the total of the sheets based on what the values in the pull down
menus. Do you have another formula besides the sumif...

I appreciate your help

 

[Ashish Mathur]

Hi,

You can also try this formula in your summary sheet

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!B9"),B9,INDIRECT("Sheet"&{1,2,3}&"!C9")))

Since you have a large number of sheets, you can use the following:

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&H9:H11&"!B9"),B9,INDIRECT("Sheet"&H9:H11&"!C9")))

where H9:H13 holds 1,2,3

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Rover]

Ashish,

My brain is fried, and I cant seem to get any formulas to work...

How would you...
Sum the totals from cells C9 (values X, Y or Z - based on what the user
selected from the pull down menu in cells C9) in Sheet 1 thru Sheet 30, based
on the criteria (pull down menus) chosen...The sum from will appear on the
Summary sheet.

Please help...

 

[Ashish Mathur]

Hi,

Cell B9 on the summary sheet and all the 30 sheets has the pull down menu
from which you can select X, Y or Z. Cell C9 of the summary sheet and all
the 30 sheets has the value to be summed up.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com