Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple sheets
I am trying to use the following formula to sum the same cell on multiple
sheets IF the critria in another cell on each sheet matches : =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8) I get a return of VALUE. Can I not use SUMIF to calculate the numbers I need? Any suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple sheets
=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C2&"'!I6"),"NP", INDIRECT("'"&C1:C2&"'!I8") )) where C1:C2 is a range housing the relevant sheetnames in separate cells. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "frustratedwthis" wrote in message ... I am trying to use the following formula to sum the same cell on multiple sheets IF the critria in another cell on each sheet matches : =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8) I get a return of VALUE. Can I not use SUMIF to calculate the numbers I need? Any suggestions? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple sheets
I tried the formula and it returns REF.
Any other suggestions? and THANKS for you r help!! "Bob Phillips" wrote: =SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C2&"'!I6"),"NP", INDIRECT("'"&C1:C2&"'!I8") )) where C1:C2 is a range housing the relevant sheetnames in separate cells. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "frustratedwthis" wrote in message ... I am trying to use the following formula to sum the same cell on multiple sheets IF the critria in another cell on each sheet matches : =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8) I get a return of VALUE. Can I not use SUMIF to calculate the numbers I need? Any suggestions? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple sheets
I hate to post this way, but this question is answered within this site.
Just keep searching for it. I wasn't able to use one thread, had to combine a couple different ones to get mine to work, but I eventually did. Sorry, but i don't have the formula anymore. I had a problem making sure I had comma's and spaces in the formula correct. Keep playing with it and you will get it. "frustratedwthis" wrote: I tried the formula and it returns REF. Any other suggestions? and THANKS for you r help!! "Bob Phillips" wrote: =SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C2&"'!I6"),"NP", INDIRECT("'"&C1:C2&"'!I8") )) where C1:C2 is a range housing the relevant sheetnames in separate cells. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "frustratedwthis" wrote in message ... I am trying to use the following formula to sum the same cell on multiple sheets IF the critria in another cell on each sheet matches : =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8) I get a return of VALUE. Can I not use SUMIF to calculate the numbers I need? Any suggestions? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple sheets
Bob's formula will work but you need to put ALL sheet names in a range so if
you have 10 sheet you would need a ten ccell range to refer to like C1::C10, so put all sheet names in the range, then all the apostrophes (for sheet names with spaces) should be there just like in Bob's formula so if you want to sum I8 in 2 sheets where I6 is "NP" then Bob's formula is correct. There is a 3D SUMIF example for download using other sheet names but the same technique here http://nwexcelsolutions.com/Download...e%20sheets.xls Regards, Peo Sjoblom "frustratedwthis" wrote: I tried the formula and it returns REF. Any other suggestions? and THANKS for you r help!! "Bob Phillips" wrote: =SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C2&"'!I6"),"NP", INDIRECT("'"&C1:C2&"'!I8") )) where C1:C2 is a range housing the relevant sheetnames in separate cells. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "frustratedwthis" wrote in message ... I am trying to use the following formula to sum the same cell on multiple sheets IF the critria in another cell on each sheet matches : =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8) I get a return of VALUE. Can I not use SUMIF to calculate the numbers I need? Any suggestions? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple sheets
Sorry, I only tested with 2 sheets, hence C1 and C2. As Peo says, you should
list all the target sheets in C1:Cn, and adjust the formula to suit. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "frustratedwthis" wrote in message ... I tried the formula and it returns REF. Any other suggestions? and THANKS for you r help!! "Bob Phillips" wrote: =SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C2&"'!I6"),"NP", INDIRECT("'"&C1:C2&"'!I8") )) where C1:C2 is a range housing the relevant sheetnames in separate cells. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "frustratedwthis" wrote in message ... I am trying to use the following formula to sum the same cell on multiple sheets IF the critria in another cell on each sheet matches : =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8) I get a return of VALUE. Can I not use SUMIF to calculate the numbers I need? Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting multiple sheets | Excel Worksheet Functions | |||
Printing Multiple sheets | Excel Discussion (Misc queries) | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
Can I unhide multiple sheets at once? | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |