countif with and

[sufianmalik]

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

 

[Aladin Akyurek]

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]

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?

 

[Aladin Akyurek]

Re: 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?

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

 

[ivory]

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

 

[jeromy]

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

 

[Aladin Akyurek]

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

http://www.mrexcel.com/board2/viewtopic.php?t=73205