Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > SumIf accross multiple worksheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 333
Default 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,


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,624
Default 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,
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 333
Default 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,

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 333
Default 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,

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default 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,



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 333
Default 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,




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default 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,






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIF across multiple worksheets triffidbook Excel Worksheet Functions 9 January 4th 18 02:23 AM
Calculate Average Accross Multiple Worksheets JK Excel Discussion (Misc queries) 1 May 3rd 07 05:34 PM
Finding matching fields accross multiple worksheets alpha417 Excel Discussion (Misc queries) 2 May 31st 06 12:46 PM
Vlookup accross multiple worksheets Excelcrazy Excel Worksheet Functions 3 December 20th 05 11:32 PM
SUMIF accross multiple columns X Excel Worksheet Functions 3 April 14th 05 05:51 PM


All times are GMT +1. The time now is 10:38 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement