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

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Karen53" wrote in message
...
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.



"JE McGimpsey" wrote:

Take a look he

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

In article ,
Karen53 wrote:

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 D2:D55 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,