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

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"),B 9,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" wrote in message
...
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