Count specific value in column based on value in another column

[Nickk1001]

Hi,

I have two columns of around 200 rows. Each column contains either Y, N or is null. I would like to count the number of 'Y's in one column but only include those rows where there is a Y in the same row on the corresponding column.

Thanks

Nick

 

[sheetspread]

countifs (you can sum countif or use sumproduct too)

 

[Nickk1001]

countifs (you can sum countif or use sumproduct too)

I have tried using the sumproduct and countif functions:

=SUMPRODUCT((COUNTIF(P2:P200,"Y")),(COUNTIF(AI2:AI200,"Y")))

but this just returns a multiple of the two columns.

 

[sheetspread]

Excel 2010
	A	B	C
1	Y	N
2	N
3		N
4	Y	Y
5	N	N
6		Y
7	Y	Y
8	N	N
9	Y
10	N	N
11
12	Y	Y
13
14			3
Sheet3

Cell Formulas
Range	Formula
C14	=SUMPRODUCT(--($A$1:$A$12="Y"),--($B$1:$B$12="Y"))

or =SUMPRODUCT(($A$1:$A$12="Y")*($B$1:$B$12="Y"))

 

[Kruwler]

=COUNTIFS(P2:P200;"y";AI2:AI200;"y")

 

[Nickk1001]

=COUNTIFS(P2:P200;"y";AI2:AI200;"y")

thanks that has worked perfectly