Sumif Query

[Guest]

I want to sum values from Column B based on criteria in column A. sumif
formula works when the range is only 1 sheet but i have in excess of 30
sheets which i want to include in the range and criteria.

example formula

=SUMIF(sheet1:sheet30!b1:b6000,K12,sheet1:sheet30!b1:b6000) only returns
#value

Does anyone have any suggestions?

 

[Guest]

Try:


=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C35&"'!A1:A100"),K12,INDIRECT("'"&C1:C35&"'!B1:B100")
))

where C1:C35 is a range housing the relevant sheetnames in separate cells.

HTH

 

[Guest]

Thanks, it's so simple when you know how.

 

[Bob Phillips]

When the sheets are structured named like that you can de it without storing
names in cells

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:30"))&"'!A1:A100"),K12,
INDIRECT(("'Sheet"&ROW(INDIRECT("1:30"))&"'!B1:B100"))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)