Summing multiple validated sheets into a summary sheet

[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

 

[Shane Devenshire]

Hi,

You need to rework this question, 1. You can't sum "x", "Y" or "Z". You can
count them? 2. If these values (x, y, z) are not in C9 or the other sheets,
where are they? 3. Are you saying that there is an X "anywhere" on Sheet3
then sum c9?

 

[Rover]

Shane,

Thank you for the response...

X, Y, Z are on the same cell on multiple sheets(30 sheets to be exact),
however, they are on a pull down menu (validation) where the user selects
whether it's X,Y or Z. I want a formula that will sum the Xs, Ys and Zs
based on what the user selected from the pull down...On my summary page, I
can put the results anywhere, lets say, A1,A2 and A3, where A1 sums the Xs,
A2 sums the Ys and A3 sums the Zs...

 

[Ashish Mathur]

Hi,

You can use the following formula. H9:H11 has 1,2,3 (for 3 sheets - please
expand this to 30 as desired). B9 has X, B10 has Y and so on. C9 is the
cell to be summed up on each sheet

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&H$9:H$11&"!$B$9"),B9,INDIRECT("Sheet"&H$9:H$11&"!$C$9")))

Also, on B9 of each sheet, you have the validated cell where the user can
select X, Y or Z.


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com