Help with countifs to sum cells

[raul8]

we need help creating several formulas using the same data below:

formula 1: Count # of times when a Y appears on column B thru E on each of the 35 rows (there must be a Y in B, C, D, E) (we expect a result of 4)
Formula 2: a formula that returns a value of 0 if a cell contains a Y and value of 1 if the cell contains an N

AlvarezD_Datamanagement.xlsx
	A	B	C	D	E	F	G	H	I
3		STK-1	STK-2	STK-3	STK-4	STK-5	STK-6	STK-8	STK-10
4	1	Y	Y	Y	N	N	Y	N	Y
5	2	Y	Y	Y	Y	Y	Y	Y	Y
6	3	Y	Y	Y	Y	Y	Y	Y	Y
7	4	Y	Y	Y	Y	Y	Y	Y	Y
8	5	N	Y	NA	Y	N	Y	Y	Y
9	6	N	Y	Y	Y	Y	Y	Y	Y
10	7	Y	Y	Y	Y	N	Y	Y	Y
11	8	Y	N	Y	Y	Y	N	N	Y
12	9	N	Y	NA	Y	N	Y	N	N
13	10	Y	Y	NA	N	Y	Y	N	Y
14	11	N	Y	NA	N	Y	N	N	N
15	12	Y	Y	NA	Y	Y	Y	Y	Y
16	13	Y	Y	N	Y	Y	Y	N	N
17	14	Y	N	Y	Y	Y	N	Y	Y
18	15	Y	Y	Y	Y	Y	Y	Y	Y
19	16	N	Y	Y	Y	Y	Y	Y	Y
20	17	Y	Y	Y	Y	N	N	N	Y
21	18	N	Y	NA	Y	Y	Y	N	Y
22	19	N	Y	Y	Y	N	Y	Y	Y
23	20	Y	N	Y	Y	Y	Y	Y	Y
24	21	Y	Y	Y	Y	Y	Y	N	Y
25	22	Y	Y	Y	Y	Y	Y	Y	Y
26	23	Y	Y	N	Y	Y	N	N	Y
27	24	Y	Y	N	Y	Y	Y	Y	Y
28	25	N	Y	NA	Y	Y	N	N	Y
29	26	Y	Y	NA	N	N	Y	Y	Y
30	27	N	N	Y	Y	N	N	N	N
31	28	N	Y	Y	N	Y	Y	Y	N
32	29	N	Y	N	N	Y	Y	Y	Y
33	30	Y	Y	Y	Y	Y	Y	Y	N
34	31	Y	Y	Y	Y	N	Y	Y	Y
35	32	Y	N	NA	Y	Y	Y	N	N
36	33	Y	Y	NA	Y	N	Y	N	Y
37	34	Y	N	Y	Y	Y	N	Y	N
38	35	N	Y	N	Y	N	Y	N	Y
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:I38	Cell Value	unique values	text	NO
I4:I38	Cell Value	="Y"	text	NO
E4:E38	Cell Value	="Y"	text	NO
H4:H38	Cell Value	="Y"	text	NO
G4:G38	Cell Value	="Y"	text	NO
F4:F38	Cell Value	="Y"	text	NO
D4:D38	Cell Value	="Y"	text	NO
C4:C38	Cell Value	="Y"	text	NO
C4:C38	Cell Value	="Y=0"	text	NO
B4:B38	Cell Value	contains "Y"	text	NO
B4:B38	Cell Value	="Y=0, N=1"	text	NO
B4:B38	Cell Value	contains "Y=1"	text	NO

 

[jasonb75]

formula 1: Count # of times when a Y appears on column B thru E on each of the 35 rows (there must be a Y in B, C, D, E) (we expect a result of 4)

How are you getting a result of 4? There are 10 rows that meet the criteria in your example.

 

[raul8]

How are you getting a result of 4? There are 10 rows that meet the criteria in your example.

Thank you! Great catch! Yes you are right, there are 10 rows that meet the criteria

 

[Toadstool]

Hi Raul8,

Formula 1 is:

=SUMPRODUCT(--($B$4:$B$38<>"")*(($B$4:$B$38="Y")*($C$4:$C$38="Y")*($D$4:$D$38="Y")*($E$4:$E$38="Y")))

...but I don't understand Formula 2? Is it just a count of all "Y"? Where does the result go?

 

[jasonb75]

For the first one,

=COUNTIFS(B:B,"Y",C:C,"Y",D:D,"Y",E:E,"Y")

For the second, it is unclear if it should be a count for a specific row, column the whole range, or something else. Regardless of which is needed you can use

=COUNTIF(range,"Y")

setting range as required

 

[raul8]

Hi Raul8,

Formula 1 is:

=SUMPRODUCT(--($B$4:$B$38<>"")*(($B$4:$B$38="Y")*($C$4:$C$38="Y")*($D$4:$D$38="Y")*($E$4:$E$38="Y")))

...but I don't understand Formula 2? Is it just a count of all "Y"? Where does the result go?

The first formula worked! Thank you, it yielded the result we expected!

 

[raul8]

For the first one,

=COUNTIFS(B:B,"Y",C:C,"Y",D:D,"Y",E:E,"Y")

For the second, it is unclear if it should be a count for a specific row, column the whole range, or something else. Regardless of which is needed you can use

=COUNTIF(range,"Y")

setting range as required

The first formula worked! Thank you, it yielded the result we expected!