SumIf accross multiple worksheets

[Karen53]

Hi,

I'm stuck.

I have multiple worksheets where I need to sum the data in the column if the
top cell contains a specific value on each worksheet. I found where I can do
a 3D sum but it doesn't allow the conditional "if"

For example:

Sheet1 Sheet2
Sheet3 etc....
A1=Yes D1=No A1=blank D1= No A1=No D1=Yes
A2=14 D2=3 A2=3 D2=5 A2=3 D2=15
A3=10 D3=15 A3=19 D3=1 A3=1 D3=11
A4=7 D4=1 A4=5 D4=22 A4=10 D4=30

Sum A2:A55 on all worksheets containing "Yes" in A1.
Sum D255 on all worksheets containing "No" in D1.

It's not always "yes" or "No". There are a few cell 1 values I would target.

Can anyone help me with this?

Thanks,

 

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/threedsumif.html

 

[Karen53]

Thank you so much!

Now my next step..

I have a validation list in A2 containing the names of the worksheets.
Depending on what is selected on the list, that is also what I want to show.
Here's what I have so far (I have sumif formulas on the individual worksheets)

=IF(ISBLANK(A2),SUM(Sheet1:Sheet5!$M11))

This works beautifully for a sumi of the worksheets.

To abtain one particular worksheets data I've tried this..

=IF(ISBLANK(A2),SUM(Sheet1:Sheet5!$M11), A2&!$M11))
which didn't work, and I've tried

=IF(ISBLANK(A2),SUM(Sheet1:Sheet5!$M11), A2&"!$M11"))
=IF(ISBLANK(A2),SUM(Sheet1:Sheet5!$M11), A2&"!"&$M11))

but none of these work. I either get an error or it says the name of the
worksheet but doesn't pull in the value of that cell.

Any suggestions?

Thanks!

 

[Karen53]

I've also tried this..
=IF(ISBLANK(A2),SUM(Sheet1:Sheet5!$M11),("="&A2&"!$M11")

it gives me exaclty the statement I need but as text, it doesn't pull in the
value of the cell referenced.

 

[T. Valko]

=IF(ISBLANK(A2),SUM(Sheet1:Sheet5!$M11),("="&A2&"!$M11")

Try it like this:

=IF(ISBLANK(A2),SUM(Sheet1:Sheet5!$M11),INDIRECT("'"&A2&"'!M11"))

 

[Karen53]

Thanks you!!!

Try it like this:

=IF(ISBLANK(A2),SUM(Sheet1:Sheet5!$M11),INDIRECT("'"&A2&"'!M11"))

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks you!!!