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?

--
gibz

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



"gibz" wrote:

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?

--
gibz

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


"Toppers" wrote:

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



"gibz" wrote:

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?

--
gibz

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:B10 0"))))

--
HTH

Bob Phillips

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

"gibz" wrote in message
...
Thanks, it's so simple when you know how.
--
gibz


"Toppers" wrote:

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



"gibz" wrote:

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?

--
gibz