SUMIF with multiple sheets

[Guest]

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?

 

[Bob Phillips]

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

 

[Guest]

I tried the formula and it returns REF.
Any other suggestions? and THANKS for you r help!!

 

[Guest]

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.

 

[Guest]

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/Sumif over multiple sheets.xls


Regards,

Peo Sjoblom

 

[Bob Phillips]

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)