Counif or Sumif with more than 1 condition?

[Mike L]

Hi everyone

Is it possible to do a COUNTIF or a SUMIF and test for more than one condition.

i.e if the values in column A = value in a cell and if the valuse in column B = another cell then add all the corresponding values in column B.

Think I've done something like this in the past but the brain is a bit slow on a Monday morning.

Cheers

-Mike

 

[Phutile]

countif w/ more than one condition:
=sum(if(a2:a10=EAST,if(b2:b10=WEST,1,0),0)

 

[Phutile]

countif w/ more than one condition:
{=sum(if(a2:a10=EAST,if(b2:b10=WEST,1,0),0)}

Make sure you hit Control-Shift-Enter at the same time when finishing the formula.

 

[PaddyD]

Hi - further to Phutile's post,

generically:

=sum(if(range1=condition1,if(range2=condition2,1)))

entered as an array formula

or

=sumproduct((range1=condition1)*(range2=condition2))

or

=dsum(range,criteria_fieldname,criteria_reference)


for extensive info on conditional counting / summing, see Aladin's post:

http://www.mrexcel.com/wwwboard/messages/8961.html


Hope that helps,

paddy

 

[Mike L]

Thanks for your help,

I didn't even know about array formulas - very very usefull.

Just another group of trick I can add to my excel vocab :wink:

-Mike