SumIf accross multiple worksheets


G

Guest

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,
 
Ad

Advertisements

G

Guest

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!
 
G

Guest

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

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"))
 
Ad

Advertisements


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

Top