SUMIF with more than 1 criteria

[Guest]

Hi

I am trying to sum cells that fulfil either criteria A or criteria B. I have
tried =SUMIF(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

 

[Don Guillett]

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

 

[Aladin Akyurek]

It is unclear what criteria are: cells or strings...

1.

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

2.

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

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

{"Orange","Kiwi"}

{10,12}

 

[Nick]

hi,

use an array formula like

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

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

Regards,

Nick
(e-mail address removed)

 

[Harlan Grove]

2.

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

....

Dangerous in the general case, e.g.,

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

This idiom only works when the criteria are mutually exclusive.