sumif across multiple sheets

[Guest]

I have the following formula that I am trying to use but the result is #VALUE:

=SUMIF(Sheet1:Sheet9!B1,"PMI",Sheet1:Sheet9!A3)

The formula should look in cell B1 of each sheet and if it is equal to "PMI"
sum of the value in cell A3 in each sheet.

What am I doing wrong or is this possible?

Thanks

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/threedsumif.html

 

[Bob Phillips]

Try putting the sheet names in C1:c9 and use

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C9&"'!B1"),"PMI",INDIRECT("'"&C1:C9&"'!A3"
)))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Biff]

If your sheet names really are Sheet1, Sheet2, Sheet3 etc:

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:9"))&"!B1"),"PMI",INDIRECT("Sheet"&ROW(INDIRECT("1:9"))&"!A3")))

Biff

 

[Guest]

The sheet names are not Sheet1, Sheet2, Sheet3 etc... I just put in for
simplicity. They can be any name.

Thanks.

 

[Guest]

Bob, here is what I typed in but it does not seem to work. Not sure why.
Thanks.

=SUMPRODUCTS(SUMIF(INDIRECT("'"&C1:C9&"'!B1"),"PMI",INDIRECT("'"&C1:C9&"'!A3")))

 

[Guest]

Bob, I figured out what my problem was. Thanks.

I have one more question. If the range C1:C9 is on another sheet, how would
I type this in?

Thanks.

 

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("'"&'other
sheet'!C1:C9&"'!B1"),"PMI",INDIRECT("'"&'other sheet'!C1:C9&"'!A3")))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Wrap around wasn't good for that

=SUMPRODUCT(SUMIF(
INDIRECT("'"&'other sheet'!C1:C9&"'!B1"),"PMI",
INDIRECT("'"&'other sheet'!C1:C9&"'!A3")))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob, I decided to open the range to use to the last cell with something in it
using the COUNTA function but this doesn't seem to work. Thanks.

=SUMPRODUCT(SUMIF(INDIRECT("'"&LU!F1:INDIRECT("F"&COUNTA(LU!F1:F1500))&"'!B1"),$A$1,INDIRECT("'"&LU!F1:INDIRECT("F"&COUNTA(LU!F1:F1500))&"'!A3")))

 

[Bob Phillips]

You know what they say, give a mouse some cheese, and he wants some milk
<vbg>

=SUMPRODUCT(SUMIF(
INDIRECT(OFFSET(F1,,,COUNTA(F:F))&"!B1"),"PMI",
INDIRECT(OFFSET(F1,,,COUNTA(F:F))&"!A3")))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob, this is what I tried and it does not work. By the way "LU" is a tab
name that I am using to lookup a name.

Thanks, Steve

=SUMPRODUCT(SUMIF(INDIRECT(OFFSET(LU!F1,,,COUNTA(LU!F:F))&"!B1"),"PMI",INDIRECT(OFFSET(LU!F1,,,COUNTA(LU!F:F))&"!A3")))

 

[Bob Phillips]

Works great for me Steve, what are you seeing?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

I am getting a #REF! error using the formulas I attached below.

Thanks, Steve

 

[Bob Phillips]

Steve,

I have posted a working example at
http://www.xldynamic.com/example code/NG - Steve - sumif across multiple sheets.xls


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob, I may have forgotten to tell you that the formula should be on a
seperate tab, not the LU tab. I was able to get it to work the way you did
also but not if the formula is on a seperate tab. Sorry for the confusion.

Thanks, Steve