SumIf accross multiple worksheets




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?





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)


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?



I've also tried this..

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

T. Valko


Try it like this:



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question