ExcelBanter - SumIf accross multiple worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SumIf accross multiple worksheets (https://www.excelbanter.com/excel-worksheet-functions/155401-sumif-accross-multiple-worksheets.html)

Karen53

SumIf accross multiple worksheets

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,

JE McGimpsey

SumIf accross multiple worksheets

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,

Karen53

SumIf accross multiple worksheets

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!

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

Karen53

SumIf accross multiple worksheets

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,

T. Valko

SumIf accross multiple worksheets

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

Karen53

SumIf accross multiple worksheets

Thanks you!!!

"T. Valko" wrote:

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

T. Valko

SumIf accross multiple worksheets

You're welcome!

--
Biff
Microsoft Excel MVP

"Karen53" wrote in message
...
Thanks you!!!

"T. Valko" wrote:

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

All times are GMT +1. The time now is 04:07 PM.