Sumif Query

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

--
gibz

[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



"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

[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

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

"gibz" <gibz@discussions.microsoft.com> wrote in message
news:05C4AE80-078C-4A85-B6B5-DA484F33011E@microsoft.com...
> 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