Sumif Query

G

gibz

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?
 
T

Toppers

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
 
B

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