# Counif or Sumif with more than 1 condition?

#### Mike L

##### New Member
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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
countif w/ more than one condition:
=sum(if(a2:a10=EAST,if(b2:b10=WEST,1,0),0)

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.

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,

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

Replies
5
Views
398
Replies
1
Views
156
Replies
16
Views
251
Replies
20
Views
892
Replies
1
Views
518

1,207,011
Messages
6,076,141
Members
446,187
Latest member
LMill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back