EXCEL COUNT YES PLUS ONE CONDITION

[Unlucky]

I have two columns, AR (Program Name) and BA (Yes or No), and I want to count the number of 'Yes' responses based on the program name. I've tried COUNT, COUNTA, COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS, SUMPRODUCT without success. I get returns of 0, all the Yes', and #VALUE. I've been trying to figure this out for over five hours, and Google keeps steering me to =COUNT(A1:A6,"Yes")! Here is the basic formula:

=COUNT('RAW DATA'!$BA$8:$BA$1507,"Yes",'RAW DATA'!$AR$8:$AR$1508=C$102)

The C$102 criteria references the program name.

And yes, I have tried the search parameters in reverse order (AR before BA).

Any help is enormously appreciated!

I really am Unlucky

 

[JamesCanale]

Your array sizes are different (1507 vs 1508). That will throw an error. Also, Count doesn't work like that.

MrExcelPlayground4.xlsx
	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
1	Program Name									Y/N
2	A		A	3						Yes
3	B		B	1						No
4	C		C	2						Yes
5	A		D	1						Yes
6	B		E	0						No
7	C									Yes
8	D									No
9	E									No
10	A									Yes
11	B									Yes
12	D									Yes
13	B									No
14	C									No
Sheet30

Cell Formulas
Range	Formula
AU2:AU6	AU2	=COUNTIFS($AR$2:$AR$14,AT2,$BA$2:$BA$14,"Yes")

 

[Unlucky]

JamesCanale

Amazing! I feel so stupid, . . . . . and unlucky. That was such a simple fix, that I can't believe I missed it. Ugh!

I genuinely appreciate your help. Thank you. Thank you. Thank you.

 

[Unlucky]

Unfortunately, I have another puzzle that I can't solve.

Similar to the above issue, I have the 'Program' name in column AR, and associated / affected programs in columns BC:BO. I want to count the number of associated times when a program name is located in column AR, and an affected program is identified in columns BC:BO. For now, column AR entries have an actual name, whereas columns BC:BO have a '1'. Should I change columns BC:BO to a name, or use 'Yes' to make the calculations easier? The current formula is:

=COUNTIFS('RAW DATA'!$AR$8:$AR$1507,AY$38,'RAW DATA'!$BA$8:$BA$1507,$BD$37)

AY$38 is a program name (ABC) that changes, and $BD$37 is a program name (QMF) that doesn't change; AY$38:BK$38 have different program names, with the idea to count correlated occurrences.

 

[JamesCanale]

I can't gather your question. Any chance you can put up some sample data and the output your looking for?

 

[Unlucky]

LOG_BAE_(06085)_OPEN-CARs_REVISED_DRAFT_2021-11-04.xlsx
	AR						AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP
6	PROGRAM						FOUND DURING		AUDIT CHARs	REPEAT DEFECT?	AFFECT OTHER PROGRAMS?	AFFECTED PROGRAMS
7							PE	PR				AAV	ACV	AMPV	BFV	M88	PIM-CAT	PIM-PIM	PFCS	M109A5	M109A6	Con Bus Sys	Facility	QMS	TOTAL
8	AAV						Yes	No	75	Yes	No		1			1									2
9	ACV						Yes	No	22	Yes	Yes					1	1	1							3
10	AAV						Yes	No	38	Yes	No		1			1									2
11	BFV						No	Yes	18	No	No
12	M88						Yes	No	22	No	No
13	PIM-CAT						Yes	No	39	Yes	Yes			1											1
14	PIM-PIM						Yes	No	51	No	No			1											1
15	PFCS						Yes	No	74	Yes	Yes			1	1										2
16	PFCS						Yes	No	35	No	No
17	M109A5						Yes	No	60	No	No
18	AAV						Yes	No	26	Yes	No		1												1
19	M109A6						Yes	No	28	No	No						1								1
20	Con Bus Sys						No	Yes	22	Yes	Yes						1								1
21	Facility						No	Yes	35	Yes	Yes							1							1
22	Facility						Yes	No	64	No	No							1							1
23	AMPV						Yes	No	31	No	No							1							1
24	M88						Yes	No	44	Yes	Yes								1						1
25	Facility						Yes	No	41	No	No									1					1
26	QMS						No	Yes	53	Yes	Yes										1				1
27	QMS						Yes	No	24	No	No											1		1	2
28	QMS						Yes	No	21	Yes	Yes													1	1
RAW DATA

Cell Formulas
Range	Formula
BC7	BC7	='DROP DOWNS'!F6
BD7	BD7	='DROP DOWNS'!F7
BE7	BE7	='DROP DOWNS'!F8
BF7	BF7	='DROP DOWNS'!F9
BG7	BG7	='DROP DOWNS'!F10
BH7	BH7	='DROP DOWNS'!F11
BI7	BI7	='DROP DOWNS'!F12
BJ7	BJ7	='DROP DOWNS'!F13
BK7	BK7	='DROP DOWNS'!F14
BL7	BL7	='DROP DOWNS'!F15
BM7	BM7	='DROP DOWNS'!F16
BN7	BN7	='DROP DOWNS'!F17
BO7	BO7	='DROP DOWNS'!F18
BP8:BP28	BP8	=SUM(BC8:BO8)

Cells with Data Validation
Cell	Allow	Criteria
AR8:AR1507	List	='DROP DOWNS'!$F$5:$F$20
AX8:AY28	List	='DROP DOWNS'!$J$5:$J$8
BA8:BB28	List	='DROP DOWNS'!$J$5:$J$8

 

[Unlucky]

LOG_BAE_(06085)_OPEN-CARs_REVISED_DRAFT_2021-11-04.xlsx
	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
35	PROGRAM CORRECTIVE ACTION REQUESTS BY REPEAT OBSERVED NON-CONFORMANCES
36
37		TOTAL CARs WRITTEN AGAINST					AAV	THAT AFFECT OTHER PROGRAMS					REPEAT PERCENTAGE		0.00%
38	CAR STATUS METRICS	AAV	ACV	AMPV	BFV	M88	PIM-CAT	PIM-PIM	PFCS	M109A5	M109A6	Con Bus Sys	Facility	QMS	TOTAL
39		0	0	0	0	0	0	0	0	0	0	0	0	0	0
40		0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
PROGRAM

Cell Formulas
Range	Formula
BD37,AY38	BD37	='DROP DOWNS'!$F$6
BL37	BL37	=($BL$39)/($BL$10)
AZ38	AZ38	='DROP DOWNS'!$F$7
BA38	BA38	='DROP DOWNS'!$F$8
BB38	BB38	='DROP DOWNS'!$F$9
BC38	BC38	='DROP DOWNS'!$F$10
BD38	BD38	='DROP DOWNS'!$F$11
BE38	BE38	='DROP DOWNS'!$F$12
BF38	BF38	='DROP DOWNS'!$F$13
BG38	BG38	='DROP DOWNS'!$F$14
BH38	BH38	='DROP DOWNS'!$F$15
BI38	BI38	='DROP DOWNS'!$F$16
BJ38	BJ38	='DROP DOWNS'!$F$17
BK38	BK38	='DROP DOWNS'!$F$18
AY39:BA39	AY39	=COUNTIFS('RAW DATA'!$AR$8:$AR$1507,AY$38,'RAW DATA'!$BC$8:$BC$1507,$BD$37)
BB39:BK39	BB39	=COUNTIFS('RAW DATA'!$AR$8:$AR$1507,BB$38,'RAW DATA'!$BA$8:$BA$1507,">0")
BL39:BL40	BL39	=SUM(AY39:BK39)
AY40:BK40	AY40	=IF(AY39="","",AY39/$P$69)

Subsequent tables will be made, and cell BD37 would change to reflect the next program to determine association.

 

[Unlucky]

I can't gather your question. Any chance you can put up some sample data and the output your looking for?

Are the attached tables of any use or help?

 

[JamesCanale]

Not at all sure this is right:

MrExcelPlayground4.xlsx
	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP
6	PROGRAM						FOUND DURING		AUDIT CHARs	REPEAT DEFECT?	AFFECT OTHER PROGRAMS?	AFFECTED PROGRAMS
7							PE	PR				AAV	ACV	AMPV	BFV	M88	PIM-CAT	PIM-PIM	PFCS	M109A5	M109A6	Con Bus Sys	Facility	QMS	TOTAL
8	AAV						Yes	No	75	Yes	No		1			1									2
9	ACV						Yes	No	22	Yes	Yes					1	1	1							3
10	AAV						Yes	No	38	Yes	No		1			1									2
11	BFV						No	Yes	18	No	No														0
12	M88						Yes	No	22	No	No														0
13	PIM-CAT						Yes	No	39	Yes	Yes			1											1
14	PIM-PIM						Yes	No	51	No	No			1											1
15	PFCS						Yes	No	74	Yes	Yes			1	1										2
16	PFCS						Yes	No	35	No	No														0
17	M109A5						Yes	No	60	No	No														0
18	AAV						Yes	No	26	Yes	No		1												1
19	M109A6						Yes	No	28	No	No						1								1
20	Con Bus Sys						No	Yes	22	Yes	Yes						1								1
21	Facility						No	Yes	35	Yes	Yes							1							1
22	Facility						Yes	No	64	No	No							1							1
23	AMPV						Yes	No	31	No	No							1							1
24	M88						Yes	No	44	Yes	Yes								1						1
25	Facility						Yes	No	41	No	No									1					1
26	QMS						No	Yes	53	Yes	Yes										1				1
27	QMS						Yes	No	24	No	No											1		1	2
28	QMS						Yes	No	21	Yes	Yes													1	1
29
30
31
32
33
34
35
36
37													AAV
38								AAV	ACV	AMPV	BFV	M88	PIM-CAT	PIM-PIM	PFCS	M109A5	M109A6	Con Bus Sys	Facility	QMS	TOTAL
39							This	0	3	0	0	2	0	0	0	0	0	0	0	0	5
40							Or	0	3	0	0	2	0	0	0	0	0	0	0	0	5
Sheet29 (2)

Cell Formulas
Range	Formula
BP8:BP28,BL39:BL40	BP8	=SUM(BC8:BO8)
AY39:BK39	AY39	=SUMPRODUCT(--($AR$8:$AR$28=$BD$37),BC8:BC28)
AY40:BK40	AY40	=COUNTIFS($AR$8:$AR$28,$BD$37,BC8:BC28,1)

 

[Unlucky]

Awesome! I used the COUNTIF version.

Sorry for the delay in responding; I had already gone home for the night when you provided the answer.

Again, thank you very much!