# countif with and

#### sufianmalik

##### Board Regular
Anyone know how to count only if two items are satisfied?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

##### MrExcel MVP
sufianmalik said:
Anyone know how to count only if two items are satisfied?

Always exemplify what you need...

Taken up generally, one way:

=SUMPRODUCT(--(RangeX=CondX),--(RangeY=CondY))

would represent a multiconditional count with "=" as the relational test.

#### sufianmalik

##### Board Regular
Thanks

The example was:

Column A contains A, B, C , D

Column B, contains just Y and N

I want to count all the A's that are also Y's

How would i fit that in?

##### MrExcel MVP
Re: Thanks

sufianmalik said:
The example was:

Column A contains A, B, C , D

Column B, contains just Y and N

I want to count all the A's that are also Y's

How would i fit that in?

=SUMPRODUCT(--(\$A\$2:\$A\$100="A"),--(\$B\$2:\$B\$100="Y"))

#### ivory

##### New Member

i'm curious to what the "--" is for in that formula =SUMPRODUCT(--(\$A\$2:\$A\$100="A"),--(\$B\$2:\$B\$100="Y"))

#### jeromy

##### New Member
Using the sum formula and entering as an array / CSE function would work as well. Enter the function and hold control+shift+enter.

http://www.cpearson.com/excel/array.htm

=SUM((\$A\$2:\$A\$100="A")*(\$B\$2:\$B\$100="Y"))

The signifigance of the dashes is as they coerce the values from bool to numeric (T/F to 1/0).

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I know the argument of the sumproduct is an array, but I think the array function is easier to look at and comprehend its intent. Different strokes. Both get you the same answer.

Jeromy

Replies
4
Views
47
Replies
0
Views
98
Replies
2
Views
157
Replies
8
Views
106
Replies
6
Views
148