SUMIF using two ranges
 

I would like to use SUMIF with two IF ranges ie:

If Column A="xxx" and Column B="yyy" then sum column D

Is this possible? I have only every used SUMIF(A:A,"xxx",D:D)

Many thanks
Yvonne

SUMIF using two ranges
 

=SUMPRODUCT(--(A2:A20="xxx"),--(B2:B20="yyy"),D2:D20)

SP cannot work on whole columns, the range has to be explicit.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"yvonneb" wrote in message
...
I would like to use SUMIF with two IF ranges ie:

If Column A="xxx" and Column B="yyy" then sum column D

Is this possible? I have only every used SUMIF(A:A,"xxx",D:D)

Many thanks
Yvonne

SUMIF using two ranges
 

Try Sumproduct:-

=SUMPRODUCT((A1:A100="xxx")*(B1:B100="yyy")*(D1:D1 00))

Mike


"yvonneb" wrote:

I would like to use SUMIF with two IF ranges ie:

If Column A="xxx" and Column B="yyy" then sum column D

Is this possible? I have only every used SUMIF(A:A,"xxx",D:D)

Many thanks
Yvonne

SUMIF using two ranges
 

Thanks Bob, got it using your suggestion. Much appreciated.

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20="xxx"),--(B2:B20="yyy"),D2:D20)

SP cannot work on whole columns, the range has to be explicit.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"yvonneb" wrote in message
...
I would like to use SUMIF with two IF ranges ie:

If Column A="xxx" and Column B="yyy" then sum column D

Is this possible? I have only every used SUMIF(A:A,"xxx",D:D)

Many thanks
Yvonne