Counting specific entries based on various conditions

[Newbienew]

Good Day,

This is hard to explain but, I have a formulas (D177 and down) that I have been using for some time to count specific conditional entries. The current formula already counts for "QUAL" and "c/O." C/O is no longer needed but we would like to keep the function of what the formula does for "QUAL." I am looking to modify the formula to account for any entries of the text QUAL in the V4:v145 range, as well as count any entries of the text of ENL or AFF if any odd row in the B4:b145 column has a date in it if there is a name in the adjacent odd number row a4:A145.

Bottom Line Up Front (BLUF) I need to count for Qual in the range of v4:v145 based on if there is a name the second row of the combined row of a4:a145 and if there is a date in adjacent cell in b4:b145, and it needs to count for the code in e4:e145. So if name one is entered in A175, The combined row of 72 73 would be counted as 1 under RI and the same would go for combined rows 82 83 for it to be counted a PDC

Sorry if it seems I explained it twice, now sure if it makes sense.

Due to 3000 cell limit I did to xl2bb

working AL example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2
3
4		C/O			PDC																	ENL
5	Name1	C/O
6		C/O			PDC																	ENL
7	Name4	C/O
8		C/O			PH
9	Name3	C/O
10		C/O			PH
11	Name4	C/O
12		C/O			RL
13	Name4	C/O
14		C/O			PDC																	ENL
15	Name2	C/O
16		C/O			RR
17	Name4	C/O
18		C/O			PDC
19	Name2	C/O
20		C/O			SN
21	Name2	C/O
22		C/O			PH
23	Name3	C/O
24		C/O			RR
25	Name2	C/O
26		C/O			RI
27	Name1	C/O
28		C/O			PH																	ENL
29	Name1	C/O
30		C/O			PDC
31	Name1	C/O
32		C/O			SN
33	Name1	C/O
34		C/O			RR																	DECL
35	Name3	C/O
36		C/O			PDC
37	Name4	C/O
38		C/O			RR
39	Name4	C/O
40		C/O			RR
41	Name2	C/O
42		C/O			PH
43	Name2	C/O
44		C/O			RR
45	Name4	C/O
46		C/O			RR
47	Name4	C/O
48		C/O			RR
49	Name1	C/O
50		C/O			PDC
51	Name3	C/O
52		C/O			PDC
53	Name1	C/O
54		C/O			PH
55	Name3	C/O
56		C/O			PDC																	qual
57	Name1	C/O
58		C/O			SN
59	Name1	C/O
60		C/O			RL
61	Name3	C/O
62		C/O			PH																	decl
63	Name3	C/O
64		C/O			PH
65	Name1
66		C/O			PH
67	Name2	C/O
68		C/O			PH
69	Name1
70		C/O			RA
71	Name3	C/O
72					RI																	QUAL
73	Name1	7-Dec-23
74		4-Dec-23			PH																	QUAL
75	Name3	7-Dec-23
76		5-Dec-23			PH
77	Name2	6-Dec-23
78		12-Dec-23			PDC
79	Name3	14-Dec-23
80		18-Dec-23			PH
81	Name3
82					pdc																	ENL
83	Name1	19-Dec-23
84
85
86
87
Sheet1

working AL example.xlsx
	A	B	C	D	E	F
176	Name1	APP	INT	QUAL'ED	PHYS	ENL/AFF
177	PH					1
178	RN
179	RL
180	TOTAL	0	0	0	0	1
181
182	RA
183	RC
184	RD
185	RI		1	1
186	RS
187	RR
188	TOTAL	0	1	1	0	0
189
190	SN
191	OA
192	TOTAL	0	0	0	0	0
193
194	PDC		1	1		2
195	TOTAL	0	0	0	0	1
196
197	WI
198	TOTAL	0	0	0	0	0
199	-
Sheet1

Cell Formulas
Range	Formula
B177,B194,B190,B182	B177	=IF(SUMPRODUCT(--(MOD(ROW($A$4:$A$147),2)=0),--ISNUMBER((""&$B$4:$B$147)+0),--($E$4:$E$147=$A177),--($A$5:$A$148=$A$176))=0,"",SUMPRODUCT(--(MOD(ROW($A$4:$A$147),2)=0),--ISNUMBER((""&$B$4:$B$147)+0),--($E$4:$E$147=$A177),--($A$5:$A$148=$A$176)))
C177,C197,C194,C190,C182	C177	=IF(SUMPRODUCT(--(MOD(ROW($A$4:$A$147),2)=1),--ISNUMBER((""&$B$4:$B$147)+0),--($E$3:$E$146=$A177),--($A$4:$A$147=$A$176))=0,"",SUMPRODUCT(--(MOD(ROW($A$4:$A$147),2)=1),--ISNUMBER((""&$B$4:$B$147)+0),--($E$3:$E$146=$A177),--($A$4:$A$147=$A$176)))
F177,F197,F194,F190,F182	F177	=IF(SUMPRODUCT(--(IF(Name=$A$176,$E$3:$E$146)=$A177)*--(IF(Name=$A$176,$V$3:$V$146)="ENL")+--(IF(Name=$A$176,$E$3:$E$146)=$A177)*--(IF(Name=$A$176,$V$3:$V$146)="AFF"))=0,"",SUMPRODUCT(--(IF(Name=$A$176,$E$3:$E$146)=$A177)*--(IF(Name=$A$176,$V$3:$V$146)="ENL")+--(IF(Name=$A$176,$E$3:$E$146)=$A177)*--(IF(Name=$A$176,$V$3:$V$146)="AFF")))
D197,D194,D190:D191,D182:D187,D177:D179	D177	=IF(SUMPRODUCT(--(IF(Name=$A$176,$E$3:$E$146)=$A177)*--(IF(Name=$A$176,$V$3:$V$146)="C/O")+--(IF(Name=$A$176,$E$3:$E$146)=$A177)*--(IF(Name=$A$176,$V$3:$V$146)="QUAL"))=0,"",SUMPRODUCT(--(IF(Name=$A$176,$E$3:$E$146)=$A177)*--(IF(Name=$A$176,$V$3:$V$146)="C/O")+--(IF(Name=$A$176,$E$3:$E$146)=$A177)*--(IF(Name=$A$176,$V$3:$V$146)="QUAL")))
E194,E197	E194	=IF(SUMPRODUCT(--(IF(Name=$A$176,$E$3:$E$146)=$A194)*--(IF(Name=$A$176,$O$3:$O$146)="MEPS"))=0,"",SUMPRODUCT(--(IF(Name=$A$176,$E$3:$E$146)=$A194)*--(IF(Name=$A$176,$O$3:$O$146)="MEPS")))
B197	B197	=IF(SUMPRODUCT(--(MOD(ROW($A$4:$A$147),2)=0),--ISNUMBER((""&$B$4:$B$147)+0),--($E$4:$E$147=$A197),--($A$5:$A$148=$A$176))>0,"WALK-INS ARE NOT APPT","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Newbienew]

The cell format formula is amazing. I would have never thought of something like that. Thank you for that nugget.

No worries about the misunderstanding. It's an everyday thing for me at work.

Thank you greatly for the catch of the meps. I did not add it on the example, but on the original, the formula is the same as when we started with the -- and all.

B197, is working as intended. If the code is WI, then there is no APP date, only an INV.

I came to get the formula rewritten for the QUAL column and got so much more. You have simplified all formulas to the point of not having to use CSE, and it is easier to type out as well. I am humbly grateful for your time, patience, and help on the whole thing, honestly.

 

[KRice]

Thanks for the update...glad to hear the formulas and formatting are working.
Two things:

1. I meant to mention this earlier...there is nothing wrong with how you originally used the double unary operator (the "--" used to coerce 1's and 0's from TRUE's and FALSE's). My comment about that was meant to suggest that you can achieve the same effect by simply multiplying the TRUE/FALSE array using the * operator...and some may find that doing so is more intuitive (and it leads to a slightly shorter formula).
2. The WI formula uses this term...ISNUMBER($B$4:$B$147)...which means a date must exist on the even row of the even-odd row pair as one of the conditions that leads to the "walk-in" message. You mentioned when WI is the code, there is no APP date, only an INV date. I believe you mentioned in post #7 that the upper date (i.e., the even row of the pair) is an APP date, while the lower date (the odd row of the pair) is an INV date. Therefore, I believe the ISNUMBER range would be $B$5:$B$148 rather than $B$4:$B$147...you mentioned the formula is working as intended, so I must be missing something?

 

[Newbienew]

I like simple, as life is less complicated.

So I just ran into a snag. We just recently put this in to google sheets and the QUAL and the ENL columns seem to not identify what is in the { } brackets. It will only pick up what ever the first one is. Is there a reason? and is there a way to adjust for that.

I can't stand google sheets by the way. It makes somethings extremely hard.

 

[Newbienew]

I figured it out. =
Arrayformula( )