SUMIF with two sets of criteria

I have the following table, and would like to sum the value of C with the
criteria A=Production, and B=External. I have so far
SUMIF(A:A,"Production",C:C)...but can't work out how to add AND B:B,"
External". In this case the return for my request will be C1 + C5

A B C
Production External £28,240
Design External £71,908
Production InterGroup £159,070
Design External £3,600
Production External £327,700

Thanks for any help offered!

Hi!

Try this:

=SUMPRODUCT(--(A1:A10="Production"),--(B1:B10="External"),C1:C10)

Note that Sumproduct will not accept entire columns as range arguments, A:A,
B:B. C:C

Biff

"luvthavodka" <[email protected]> wrote in message
news:[email protected]...
>I have the following table, and would like to sum the value of C with the
> criteria A=Production, and B=External. I have so far
> SUMIF(A:A,"Production",C:C)...but can't work out how to add AND B:B,"
> External". In this case the return for my request will be C1 + C5
>
> A B C
> Production External £28,240
> Design External £71,908
> Production InterGroup £159,070
> Design External £3,600
> Production External £327,700
>
> Thanks for any help offered!
>

Try this:

=SUMPRODUCT((A1:A100="Production")*(B1:B100="External")*C1:C100)

You can't use total column references (A:A).

Also, you could refer to cells containing your criteria, instead of
'hard-coding' them in the formula itself.
That makes criteria changes easier to accomplish.

=SUMPRODUCT((A1:A100=D1)*(B1:B100=D2)*C1:C100)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"luvthavodka" <[email protected]> wrote in message
news:[email protected]...
> I have the following table, and would like to sum the value of C with the
> criteria A=Production, and B=External. I have so far
> SUMIF(A:A,"Production",C:C)...but can't work out how to add AND B:B,"
> External". In this case the return for my request will be C1 + C5
>
> A B C
> Production External £28,240
> Design External £71,908
> Production InterGroup £159,070
> Design External £3,600
> Production External £327,700
>
> Thanks for any help offered!
>

Thats great, how could i also add the criteria so i could see the cumulative
total at week eg18 or less, from col D, which looks as follows:

D
Week No.
16
17
18
18
19
20

I've tried the following, but it doesn't seem to work?

=SUMPRODUCT(--(A1:A10="Production"),--(B1:B10="External"),--(D1:D10>=20),C1:C10)

Thanks

"Biff" wrote:

> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(A1:A10="Production"),--(B1:B10="External"),C1:C10)
>
> Note that Sumproduct will not accept entire columns as range arguments, A:A,
> B:B. C:C
>
> Biff
>
> "luvthavodka" <[email protected]> wrote in message
> news:[email protected]...
> >I have the following table, and would like to sum the value of C with the
> > criteria A=Production, and B=External. I have so far
> > SUMIF(A:A,"Production",C:C)...but can't work out how to add AND B:B,"
> > External". In this case the return for my request will be C1 + C5
> >
> > A B C
> > Production External £28,240
> > Design External £71,908
> > Production InterGroup £159,070
> > Design External £3,600
> > Production External £327,700
> >
> > Thanks for any help offered!
> >
>
>
>

Why didn't you use

=SUMPRODUCT(--(A1:A10="Production"),--(B1:B10="External"),--(D1:D10<=18),C1:
C10)


--
HTH

Bob Phillips

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

"luvthavodka" <[email protected]> wrote in message
news:[email protected]...
> Thats great, how could i also add the criteria so i could see the
cumulative
> total at week eg18 or less, from col D, which looks as follows:
>
> D
> Week No.
> 16
> 17
> 18
> 18
> 19
> 20
>
> I've tried the following, but it doesn't seem to work?
>
>
=SUMPRODUCT(--(A1:A10="Production"),--(B1:B10="External"),--(D1:D10>=20),C1:
C10)
>
> Thanks
>
> "Biff" wrote:
>
> > Hi!
> >
> > Try this:
> >
> > =SUMPRODUCT(--(A1:A10="Production"),--(B1:B10="External"),C1:C10)
> >
> > Note that Sumproduct will not accept entire columns as range arguments,
A:A,
> > B:B. C:C
> >
> > Biff
> >
> > "luvthavodka" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have the following table, and would like to sum the value of C with
the
> > > criteria A=Production, and B=External. I have so far
> > > SUMIF(A:A,"Production",C:C)...but can't work out how to add AND B:B,"
> > > External". In this case the return for my request will be C1 + C5
> > >
> > > A B C
> > > Production External £28,240
> > > Design External £71,908
> > > Production InterGroup £159,070
> > > Design External £3,600
> > > Production External £327,700
> > >
> > > Thanks for any help offered!
> > >
> >
> >
> >

You set your criteria for *greater* then 20 [ (D1:D10>=20) ], not *less
then* [ (D1:D10<=20) ]


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"luvthavodka" <[email protected]> wrote in message
news:[email protected]...
> Thats great, how could i also add the criteria so i could see the
cumulative
> total at week eg18 or less, from col D, which looks as follows:
>
> D
> Week No.
> 16
> 17
> 18
> 18
> 19
> 20
>
> I've tried the following, but it doesn't seem to work?
>
>
=SUMPRODUCT(--(A1:A10="Production"),--(B1:B10="External"),--(D1:D10>=20),C1:
C10)
>
> Thanks
>
> "Biff" wrote:
>
> > Hi!
> >
> > Try this:
> >
> > =SUMPRODUCT(--(A1:A10="Production"),--(B1:B10="External"),C1:C10)
> >
> > Note that Sumproduct will not accept entire columns as range arguments,
A:A,
> > B:B. C:C
> >
> > Biff
> >
> > "luvthavodka" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have the following table, and would like to sum the value of C with
the
> > > criteria A=Production, and B=External. I have so far
> > > SUMIF(A:A,"Production",C:C)...but can't work out how to add AND B:B,"
> > > External". In this case the return for my request will be C1 + C5
> > >
> > > A B C
> > > Production External £28,240
> > > Design External £71,908
> > > Production InterGroup £159,070
> > > Design External £3,600
> > > Production External £327,700
> > >
> > > Thanks for any help offered!
> > >
> >
> >
> >