SUMIF with more than 1 criteria
 

Hi

I am trying to sum cells that fulfil either criteria A or criteria B. I have
tried =SUMIF(P:P,A1orB1,G:G). I can not simply add two SUMIF's together as I
need to reduce the length of the overall formula.

Thanks for your help

try
=sumproduct((P2:p200={a1,b1})*g2:g200)

--
Don Guillett
SalesAid Software

"Mike@Q" wrote in message
...
Hi

I am trying to sum cells that fulfil either criteria A or criteria B. I
have
tried =SUMIF(P:P,A1orB1,G:G). I can not simply add two SUMIF's together as
I
need to reduce the length of the overall formula.

Thanks for your help

It is unclear what criteria a cells or strings...

1.

=SUMPRODUCT(($P$2:$P$100=X1)+($P$2:$P$100=Y1),$G$2 :$G100)

2.

=SUM(SUMIF(P:P,{Crit1,Crit2},G:G))

A ctiterion that is string must be put between double quotes like in:

{"Orange","Kiwi"}

{10,12}

Mike@Q Wrote:
Hi

I am trying to sum cells that fulfil either criteria A or criteria B. I
have
tried =SUMIF(P:P,A1orB1,G:G). I can not simply add two SUMIF's together
as I
need to reduce the length of the overall formula.

Thanks for your help


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=320407

hi,

use an array formula like

SUM(IF((FirstRangedName=FirstCriteria)*(SecondRang edName=SecondCriteris);RangeToSum;0))

Don't forget that * stands for AND , +for OR

Regards,

Nick

"Don Guillett" wrote in message ...
try
=sumproduct((P2:p200={a1,b1})*g2:g200)

--
Don Guillett
SalesAid Software

"Mike@Q" wrote in message
...
Hi

I am trying to sum cells that fulfil either criteria A or criteria B. I
have
tried =SUMIF(P:P,A1orB1,G:G). I can not simply add two SUMIF's together as
I
need to reduce the length of the overall formula.

Thanks for your help

"Aladin Akyurek" wrote...
....
2.

=SUM(SUMIF(P:P,{Crit1,Crit2},G:G))
....

Dangerous in the general case, e.g.,

=SUM(SUMIF(P:P,{"a*","*z"},G:G))

This idiom only works when the criteria are mutually exclusive.