Sumif with two criterias

I have mutiple sheets of information for which i want to sum values in column
p if columns c & d meet specified criteria.

The formula i have at present is

=(SUMPRODUCT(SUMIF(INDIRECT("'"&$AE$11:$AE$26&"'!$d$9:$d$65536"),$A11,INDIRECT("'"&$AE$11:$AE$26&"'!p$9:p$65536"))))

This works for one set of criteria but i don't know how to incorprate the
second.

Can any one help/
--
gibz

What are your criteria for columns C and D and I can assist.

"gibz" wrote:

> I have mutiple sheets of information for which i want to sum values in column
> p if columns c & d meet specified criteria.
>
> The formula i have at present is
>
> =(SUMPRODUCT(SUMIF(INDIRECT("'"&$AE$11:$AE$26&"'!$d$9:$d$65536"),$A11,INDIRECT("'"&$AE$11:$AE$26&"'!p$9:p$65536"))))
>
> This works for one set of criteria but i don't know how to incorprate the
> second.
>
> Can any one help/
> --
> gibz

perhaps a simpler version
=sumproduct((c2:c22="joe")*(d2:d22=35)*p2:p22)

--
Don Guillett
SalesAid Software
[email protected]
"gibz" <[email protected]> wrote in message
news:[email protected]...
>I have mutiple sheets of information for which i want to sum values in
>column
> p if columns c & d meet specified criteria.
>
> The formula i have at present is
>
> =(SUMPRODUCT(SUMIF(INDIRECT("'"&$AE$11:$AE$26&"'!$d$9:$d$65536"),$A11,INDIRECT("'"&$AE$11:$AE$26&"'!p$9:p$65536"))))
>
> This works for one set of criteria but i don't know how to incorprate the
> second.
>
> Can any one help/
> --
> gibz

Hi,

Column c is a number 2,3,4 etc and column d is text (i.e TP010)

Don, your formula works fine on a single sheet but i have approx 35 sheet
hence why the sheets are named in a specifed range.

Thanks for your help.
--
gibz


"Barb Reinhardt" wrote:

> What are your criteria for columns C and D and I can assist.
>
> "gibz" wrote:
>
> > I have mutiple sheets of information for which i want to sum values in column
> > p if columns c & d meet specified criteria.
> >
> > The formula i have at present is
> >
> > =(SUMPRODUCT(SUMIF(INDIRECT("'"&$AE$11:$AE$26&"'!$d$9:$d$65536"),$A11,INDIRECT("'"&$AE$11:$AE$26&"'!p$9:p$65536"))))
> >
> > This works for one set of criteria but i don't know how to incorprate the
> > second.
> >
> > Can any one help/
> > --
> > gibz

Hi,

Column c is a number 2,3,4 etc and column d is text (i.e TP010)

Don, your formula works fine on a single sheet but i have approx 35 sheet
hence why the sheets are named in a specifed range.

Thanks for your help.
--
gibz


"Barb Reinhardt" wrote:

> What are your criteria for columns C and D and I can assist.
>
> "gibz" wrote:
>
> > I have mutiple sheets of information for which i want to sum values in column
> > p if columns c & d meet specified criteria.
> >
> > The formula i have at present is
> >
> > =(SUMPRODUCT(SUMIF(INDIRECT("'"&$AE$11:$AE$26&"'!$d$9:$d$65536"),$A11,INDIRECT("'"&$AE$11:$AE$26&"'!p$9:p$65536"))))
> >
> > This works for one set of criteria but i don't know how to incorprate the
> > second.
> >
> > Can any one help/
> > --
> > gibz