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]

Checking in for follow up.

 

[KRice]

You appear to be using a named range...something called "Name". What does that refer to? And just to confirm...you are using Excel 2016?

 

[KRice]

I don't entirely follow your explanation, but I have a few suggestions.

1. Generally it is a bad idea to use merged cells. I see they are used sporadically as one moves down a column. Merged cells make the worksheet difficult to understand and debug...and in some cases can lead to errors. I would try to avoid them if at all possible.
2. The formula component used for determining whether column B contains a date (based on whether the cell contains a number) can be simplified. There is no reason to coerce the cell to text with a concatenation operation and then attempt to coerce the result back to a number with an addition operation when the original cell value can simply be evaluated with ISNUMBER. I've offered this simplification in the example below.
3. Within the SUMPRODUCT functions, when multiple arrays consisting of TRUE's and FALSE's are used as arguments, rather than coercing each array separately to 1's and 0's using the double unary operator (--), it is more convenient to multiply the TRUE/FALSE arrays together. Doing so (by virtue of the arithmetic operator) automatically coerces 1's and 0's from each array without requiring the double unary operator.
4. Rather than repeat the core part of each formula twice--once to determine if it results in a 0--and if so, to return a blank--and if it does not, then return the formula result...consider having the core part of the formula return the value and either leave the 0's in the table or format the table cells to suppress 0 (using either Conditional Formatting or custom formatting).
5. In the formulas that use range references only for direct argument inputs, you might want to consider using the COUNTIFS function. The logical AND and OR operations can be incorporated in that function as shown below. For example, the current formula involving matches for "C/O" and "QUAL" uses an array of those values (so two separate arrays will be generated...one for the $V$3:$V$146="C/O" condition and the other for the $V$3:$V$146="QUAL" condition)...and this represents the OR condition. Those two arrays also require that $E$3:$E$146=$A177 (this represents the AND condition). Then the two arrays are wrapped in a SUM function to determine the count of entries satisfying these conditions. If you no longer need the "C/O" feature, then the "or" condition goes away and you don't need the SUM wrapper function, so the formula can be simplified further as shown in the lower example.

Neither of these offerings may meet your needs without some refinement, as neither incorporates the IF(Name=$A$176 component that I asked about. What does "Name" refer to?

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

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

MrExcel_20231219.xlsx
	A	B	C	D	E	F
200	Name1	APP	INT	QUAL'ED	PHYS	ENL/AFF
201	PH	0	0	1		1
202	RN			0
203	RL			0
204	TOTAL	0	0	0	0	1
205
206	RA	0	0	0		0
207	RC			0
208	RD			0
209	RI		1	1
210	RS			0
211	RR			0
212	TOTAL	0	1	1	0	0
213
214	SN	0	0	0		0
215	OA			0
216	TOTAL	0	0	0	0	0
217
218	PDC	0	1	1	0	4
219	TOTAL	0	0	0	0	1
220
221	WI		0	0	0	0
222	TOTAL	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B201,B218,B214,B206	B201	=SUMPRODUCT((MOD(ROW($A$4:$A$147),2)=0)*ISNUMBER($B$4:$B$147)*($E$4:$E$147=$A201)*($A$5:$A$148=$A$176))
C201,C221,C218,C214,C206	C201	=SUMPRODUCT((MOD(ROW($A$4:$A$147),2)=1)*ISNUMBER($B$4:$B$147)*($E$3:$E$146=$A201)*($A$4:$A$147=$A$176))
F201,F221,F218,F214,F206	F201	=SUM(COUNTIFS($E$3:$E$146,$A201,$V$3:$V$146,{"ENL","AFF"}))
D201:D203,D221,D218,D214:D215,D206:D211	D201	=COUNTIFS($E$3:$E$146,$A201,$V$3:$V$146,"QUAL")
E218,E221	E218	=COUNTIFS($E$3:$E$146,$A218,$O$3:$O$146,"MEPS")
B221	B221	=IF(SUMPRODUCT((MOD(ROW($A$4:$A$147),2)=0)*ISNUMBER($B$4:$B$147)*($E$4:$E$147=$A221)*($A$5:$A$148=$A$176))>0,"WALK-INS ARE NOT APPT","")

 

[Newbienew]

@KRice
First, thank you for your response and help with my submission. I am not the originator of the formulas or the layout of the spreadsheet. Normally I try 1 cell for 1 purpose if I can. As my name suggests, I am still learning Excel as it has a lot to it; some of what you mentioned in your last post made sense, while other parts went over my head. With that, your suggestions are more than welcome, especially if they are simplified ways to get the exact same results. This will help me understand better and grow my knowledge as well.

To answer your question, provide a bit more clarity, and ask you for clarity;

You appear to be using a named range...something called "Name". What does that refer to? And just to confirm...you are using Excel 2016?
For context, when the formula was created and given to me, it already had a "name", but it refers to the range A4:A145.
All users of this spread use EXCEL 2016 from my knowledge base.

Clarity
The APP, INT, QUAL,PHYS, columns are all dependent on the factors of if there is a name (a4:a145), a date(b4:b145), and a code (e4:e145) for them to be counted for. If there is no name, it cannot be counted for the worker. If there is no date or if the date says C/O, it will not be counted, and the same goes for no code entry.

For APP it will only count if there is a name, the matching code, and if there is a date in the first cell of the combined rows.

For INT, it is exactly the same as APP but only counts the second cell out of the combined rows.

For Qual, it is supposed to count all QUAL entries in V4:v145 if there is a name in the second cell of the combined rows of a4:a145, if there is a date in the second cell of the combined rows of b4:b145, and the matching code from e4:e145. In addition, it also counts for ENL and AFF if they match the same criteria. Although the ENL/AFF column counts for both entries, it counts regardless of dates in b4:b145.

Column B, holds a lot of weight depending on what cell is needed for the calculation, especially for the INT and QUAL.


C/O in will not be counted

Please let me know if this added clarity or if you have any other questions I can possibly answer.

Asking for Clarity
So I dont know who -- actually works. I have been trying to understand it for a while. As I had another formula provided to me with it and thought it was a typo at the time. What does it do?

I attempted to use your formula on the original spreadsheet in cell 177, and a 0 was displayed regardless of what changes that happen with in the ranges. I added a date and PH but nothing changed. Did I do something wrong?

 

[KRice]

Thanks for the clarification. I assumed "Name" referred to some range in column A, but thought it strange that only the A column range would be named when other similar ranges needed by the formulas are not named. The specific range of this variable matters, as it dictates a pattern that is critical to the formulas. You've mentioned A4:A145, so starting on the first visible green cell in the Name column, we find an empty cell. The following cell, A5, does contain a name...and it appears the names continue with an alternating pattern. So when the formula mentions Name=$A$176, and we assume A176 has some text other than a blank, we automatically know the resulting array will be {FALSE,?,FALSE,?,FALSE,?,...,FALSE,?}, where the FALSE's are due to blanks in the A column, and the question marks will be replaced with TRUE or FALSE depending on what the actual text is. If Name had referred to A5:A146, we would still have an array of 142 elements, but it would have the form {?,FALSE,?,FALSE,...,?,FALSE}. As mentioned, I left this part of the formula out of my suggestion, so the col D,E, and F formulas need some attention to add this logical constraint.

Jumping to the bottom of your last post...the double unary operator (--) converts an array consisting of TRUE/FALSE values to 1's and 0's. Consider any of the array-building components within your formulas...for example, ($E$4:$E$147=$A177) will determine whether each value in the range E4:E147 is the same as the value in A177 and either return a TRUE or FALSE result for each comparison. So this component of the formula is an array consisting of 144 elements, each of which is either TRUE or FALSE. If I wanted to count the number of TRUE values, I could use the double unary to convert the TRUE's to 1's and the FALSE's to 0's, and then SUM the entire array. So in your original formula set, the double unary was used a lot to convert each array formed by some logical comparison into arrays of 1's and 0's. And then SUMPRODUCT multiples all of these arrays of 1's and 0's together to create a single resultant array (that also consists of only 1's and 0's)...and this resultant array is finally summed for the final count. But when you have a series of these arrays formed by logical comparisons and you want all of the conditions to be true, you can directly multiply the TRUE/FALSE arrays together (using the standard multiplication operator (*)) and the TRUE's and FALSE's will automatically be "coerced" to 1's and 0's, achieving the same result.

Structurally, your worksheet has some issues. The merged cells are bound to cause some problems. And the odd row/even row requirement means that if you insert a single row above your data source table, any comparisons depending on this scheme will fail. I don't know any details about what is represented in the data table, but presumably every even-odd row pair is somehow related, and it appears that the names always appear in the odd row? At a minimum, it might be better to establish criteria based on the array index rather than the actual row number. Then the formulas would not be vulnerable to an innocent row insertion above the table. For example, modifying the expression that evaluates whether the row is odd/even by converting the value to a 0-indexed array looks like this: (MOD(ROW($A$5:$A$148)-ROW($A$5),2)=0)...and this preserves the original alternating True/False pattern, regardless of whether rows are inserted above the data table (note that I inserted a row, which caused the formula to automatically adjust the range to cover rows 5:148 rather than 4:147. The row insertion broke the other formulas that depend only on row number rather than array index.

For the previously posted simplified B177 formula:

=SUMPRODUCT((MOD(ROW($A$4:$ A$147),2)=0)*ISNUMBER($B$4:$B$147)*($E$4:$E$147=$A177)*($A$5:$A$148=$A$176))

...it requires that the row number be even, and on that same row the following must be true: a number appears in col B, and col E contains a value that matches A177 ("PH" in this case)...and then on the next row (note you have a shift in the range reference from 4:147 to 5:148...so now this means we're looking at an odd-numbered row), col A has a value of "Name1". Have a look at this point carefully and let me know if this is really what you want (I believe it is based on your most recent clarification, but would like to confirm). The formulas mention ranges for both columns A and E that sometimes cover rows 3:146, 4:147, and 5:148...and I don't quite follow the rationale for these shifts. I can understand the 4:147 and 5:148 for looking using a single element offset for arrays to identify the correct even-odd row pairs matching a particular name, but then I don't understand why a 3:146 range would be needed.

The biggest challenge is running debugging tests, as the merged cells make it impossible to select certain cells, and when navigating with arrows, one might expect to land on a cell directly in line with the arrow key used, but the merged cells cause the active cell to be shifted...sometimes. For example, depending on how I get to cell E66 (a "PH"), sometimes when I hit the left arrow four times, I land in cell A67 and other times I land in cell A66...which is counterintuitive. Is there a compelling reason to retain the merged cells? Using E66 as an example, if I type a 1 in B66 (which simulates a date because it is a number), and I type Name1 in A66...I do not see this entry accounted for, but that is because the $A$5:$A$148=$A$176 requires "Name1" to appear on the next line, meaning cell A67. When I enter Name1 into A67, the count is increased by one in the results table.

For Qual, it is supposed to count all QUAL entries in V4:v145 if there is a name in the second cell of the combined rows of a4:a145, if there is a date in the second cell of the combined rows of b4:b145, and the matching code from e4:e145.

"...if there is a date in the second cell of the combined rows..." Is this a new requirement? Your original formula for the col. D Qual summary shows no reference to the date column B.

For APP it will only count if there is a name, the matching code, and if there is a date in the first cell of the combined rows.

For INT, it is exactly the same as APP but only counts the second cell out of the combined rows.

These aren't clear. When you say "in the first cell of the combined rows", what are you referring to? The first cell of an even-odd-row pair is the upper leftmost cell, which is a blank in the name field (I think). And the name for that even-odd row pair appears on the 2nd row of the pair (or said differently, on a odd-numbered row). The "codes" appear to always be on even-rows (and unfortunately, merged with an odd row). From what I can guess, the name array index needs to shift by one (because names appear in odd rows) and the corresponding even row for that same even-odd pair needs to have the correct code and a date. When you say "but only counts the second cell out of the combined rows", what does this mean? I'm assuming this refers to the 2nd row of each even-odd row pair (so the odd rows), but what specifically is found on these odd rows that is to be counted? No codes are found on these odd-numbered rows, so the formula will return a 0.

 

[Newbienew]

Thank you for explaining the (--). Seeing how I am still not familiar with using it or how to implement it. Think I will refrain from attempting to.


Is there a compelling reason to retain the merged cells?
The format of this spreadsheet is pretty set in stone and used by a lot of people. I am fully aware of merged cells, and everyone who works with Excel who is experienced avoids merging cells. I at times become heavily reluctant to ask for help as it normally pops up in a reply of how bad it is. I have another posting with conditional formatting issues, and it's because of merged cells.

I don't know any details about what is represented in the data table, but presumably every even-odd row pair is somehow related, and it appears that the names always appear in the odd row?
Yes, in the even-odd row pairing, the name will appear in the odd row. The even row normally has data that is not used for anything in particular.

"...if there is a date in the second cell of the combined rows..." Is this a new requirement? Your original formula for the col. D Qual summary shows no reference to the date column B.
Yes, this is a new requirement, as in the original, it was overlooked for a period of time. The old requirement counted QUAL and C/O in column V regardless of whether there was a date or not. This would throw the numbers off, as only current month dates would be entered in the INT odd row. If the date entered was a previous month, C/O would be entered in place of the date regardless of even or odd row in the B column. The need to count for C/O in the V column was redundant. The new requirement is that C/O is not to be counted in the V column. But ENL, AFF, and Qual would be counted based on the odd row in column B being added to the formula, similar to how the APP and INT columns run.

These aren't clear. When you say "in the first cell of the combined rows", what are you referring to? The first cell of an even-odd-row pair is the upper leftmost cell, which is a blank in the name field (I think)
Yes. Out of the even-odd row pairing, the top cell or the first cell, which would be in column B, would be the APP date. The second cell would have the INT date.

When you say "but only counts the second cell out of the combined rows", what does this mean? I'm assuming this refers to the 2nd row of each even-odd row pair (so the odd rows), but what specifically is found on these odd rows that is to be counted?
On the Odd row in column A, there would be the name, and column B would be the INT date. So B177 will only count if out of the odd-even pairing if the even row in column B has a date, the odd row in A has a name to account for that person, and if it matches the same code.
The same process happens for INT. It is just that everything is based on the odd row now for the date.
The Qual

 

[KRice]

These clarifications were very helpful...thank you. Regarding any reluctance to seek help due to concerns about potentially critical feedback, I would encourage you to set aside those concerns. Such criticisms are typically made by those of us who have learned the hard way---by making a lot of mistakes and learning from those mistakes. Eventually, as skills develop, many of those hard-learned lessons transform into advice to pass along to others when the opportunity arises, often without knowing whether the recipient is aware of the issue, or whether they are in a position to address it. The takeaway message, however, is that the advice is intended to be constructive.

Now that we've been through a few iterations to clarify some matters, I'd like you to have a look at the example below to determine if it does what you want. I'm almost certain that you do not need to use the MOD/ROW component of the formulas because your data table structure and the way even-odd row pairs are maintained in the table can be used to ensure the relevant records are being counted. This is because the array index-shifting method used here essentially treats the specified content from the odd row (of the even-odd row pair) as though it appears beside the content on the even row (of the same even-odd row pair). So we can apply basic array multiplication to ensure that all of the conditions are met. The table below attempts to illustrate what I'm talking about. We have two sets of even-odd row pairs in the first three columns. When we reference the name or date column using a range reference shifted by 1---(because names always appear on the lower (odd) row of the pair and some dates (INT dates) are also on that same odd row---this effectively makes the resulting arrays resemble those shown in the rightmost four columns. Since the even- and odd-row content appears at the same relative array location, there is no need to use a MOD/ROW feature in the formula.

Name	Date	Code		Name	INT Date	APP Date	Code
	45264	PH		Name1	45267	45264	PH
Name1	45267
	45265	PH		Name1	45266	45265	PH
Name1	45266

If we dissect one of the formulas (the APP formula here), the main table is assumed to cover the range from rows 4:147...beginning with the first even-odd row pair and ending with the last even-odd row pair. So where you see a range reference from 4:147, we are starting those arrays with the even row from the first row pair (done here to look for a date (column B) in the even rows and a matching code in column E ); and where you see a 5:148, we are starting those arrays with the odd row from the first row pair (done here to look for a matching name in column A).

=SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$4:$B$147)*($E$4:$E$147=$A177))

I used a different formula construction for the ENL/AFF formula, primarily because no dates are involved, and the COUNTIFS function can handle the range references and ENL/AFF array.

I have not done anything further with the MEPS formula (I don't think we discussed it, did we?)...but I doubt the range covered 3:146 is correct. What counting rules apply for MEPS?
Similarly, I am not sure about the B197 formula (the Walk-Ins message). I made some changes, but wanted to confirm what are you are looking for...is it both a Date and matching code in the even row of the even-odd row pair, and a name in the odd row of the even-odd row pair?

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

Cell Formulas
Range	Formula
B177,B194,B190,B182	B177	=SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$4:$B$147)*($E$4:$E$147=$A177))
C177,C197,C194,C190,C182	C177	=SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$5:$B$148)*($E$4:$E$147=$A177))
F177,F197,F194,F190,F182	F177	=SUM(COUNTIFS($A$5:$A$148,$A$176,$E$4:$E$147,$A177,$V$4:$V$147,{"ENL","AFF"}))
D177:D179,D197,D194,D190:D191,D182:D187	D177	=SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$5:$B$148)*($E$4:$E$147=$A177)*($V$4:$V$147="QUAL"))
E194,E197	E194	=COUNTIFS($E$3:$E$146,$A194,$O$3:$O$146,"MEPS")
B197	B197	=IF(SUMPRODUCT(ISNUMBER($B$4:$B$147)*($E$4:$E$147=$A197)*($A$5:$A$148=$A$176)),"WALK-INS ARE NOT APPT","")

 

[Newbienew]

Referring to your chart with Odd-even rows having their own columns was an older version of what you see here. There were other odd-even rows in the worksheet that were their own columns as well. A lot of locations were using this but the current layout came from a manual that has the exact same layout. Attempting to change the layout only resulted in reinforcing what was in the manual.

I have applied your formula to my test copy, and it seems to work with no issues I have noticed. In an earlier response of yours, you mentioned in order to get rid of the 0s to use a conditional format and make them the same color. Could I use =if(formula=0, true, formula). It is understood the "IF" formula has to meet the criteria to execute the calcining formula first.

The Qual formula works but is missing the second part. It would also need to count for any entries of ENL and AFF
I went on a on a limb and did this
=SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$5:$B$148)*($E$4:$E$147=$A177)*($V$4:$V$147="QUAL"))+SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$5:$B$148)*($E$4:$E$147=$A177)*($V$4:$V$147={"ENL","AFF"}))

It appears to work, but I am not sure how { } characters work in Excel and what formulas you can apply them to

I have not done anything further with the MEPS formula (I don't think we discussed it, did we?)...but I doubt the range covered 3:146 is correct. What counting rules apply for MEPS? Similarly, I am not sure about the B197 formula (the Walk-Ins message). I made some changes, but wanted to confirm what are you are looking for...is it both a Date and matching code in the even row of the even-odd row pair, and a name in the odd row of the even-odd row pair?

Yes, you would be correct in we did not talk about the PHYS column. It was not a concern as it was already functioning. However, your simplified way is much easier and I do not have to use CSE to use it. The PHYS column is similar to the ENL/AFF. Only to count for entries of MEP regardless of date or not.

For the B197, it's a bit backward from the other requirements. If there is a name in the odd row, date in the even row out of the even-odd rows, and the code is "WI", then it should not be counted. The "Walk-in" message is displayed to indicate there is a entry error that needs to be corrected.

 

[KRice]

Regarding the 0's, I suggested that you might want to consider using Custom Formatting where you would enter the Custom format type as: 0;-0;;@

For any cells using this custom format, 0's will still occupy the cells, but their appearance will be suppressed. Select the cells of interest (I held down Ctrl and selected all five colored regions to do this in one step), press Ctrl+1 or from the Home menu, go to Format>Format Cells, then select Number>Custom and enter the format string shown above and confirm with OK. You could opt for something like =if(formula=0, "", formula) to display blanks rather than 0's, but this approach still involves repeating the formula...and it would be beneficial to not do that.

I misunderstood the additional conditions for the QUAL formula. I thought ENL and AFF were only involved in the ENL/AFF formula. To expand the constraints for the QUAL formula such that column V may contain either QUAL or ENL or AFF, you can enclose all three text strings within curly braces (which establishes an array of three elements), and this portion of the formula will create a three column array describing whether the V column value is QUAL or ENL or AFF (and return a TRUE or FALSE for each of those evaluations). Then all of the other arrays within SUMPRODUCT will be multiplied with this 3-column array to create a final 3-column array that accounts for all conditions including QUAL for the 1st column of the array, all conditions including ENL for the 2nd column, and all conditions including AFF for the 3rd column...and SUMPRODUCT will add all together for the total.

I'm assuming there is one aspect in the ENL/AFF formula that should be reflected in the MEPS formula. As currently written, the MEPS formula does not involve any Name term, which the ENL/AFF formula does address. So, this revision requires a matching Name (matching to A176) in the odd row (of the even-odd row pair), and a matching code (col E code in the even rows of the source data table matches the col A code in the summary table), and col O in the even rows of the source data table contains MEPS.

I'm not quite following the B197 WI explanation. SUMPRODUCT in that formula is actually counting those entries meeting the criteria you've described; however, the result of SUMPRODUCT is the logical argument in an IF statement, so any count other than 0 will be interpreted as TRUE...and for the TRUE condition, the "walk-ins" message is displayed, otherwise a blank is displayed. Does this work as intended?

Also, for the sake of consistency, I've offered SUMPRODUCT versions of some formulas that were previously shown with the COUNTIFS function. You could use either, but since several formulas are well suited for SUMPRODUCT, it might make sense to retain the same function and construction for all.

MrExcel_20231219.xlsx
	A	B	C	D	E	F
176	Name1	APP	INT	QUAL'ED	PHYS	ENL/AFF
177	PH	3	3	2		2
178	RN
179	RL
180	TOTAL	0	0	0	0	1
181
182	RA
183	RC
184	RD
185	RI			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
195	TOTAL	0	0	0	0	1
196
197	WI	WALK-INS ARE NOT APPT	1
198	TOTAL	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B177,B194,B190,B182	B177	=SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$4:$B$147)*($E$4:$E$147=$A177))
C177,C197,C194,C190,C182	C177	=SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$5:$B$148)*($E$4:$E$147=$A177))
F177,F197,F194,F190,F182	F177	=SUMPRODUCT(($A$5:$A$148=$A$176)*($E$4:$E$147=$A177)*($V$4:$V$147={"ENL","AFF"}))
D177:D179,D197,D194,D190:D191,D182:D187	D177	=SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$5:$B$148)*($E$4:$E$147=$A177)*($V$4:$V$147={"QUAL","ENL","AFF"}))
E194,E197	E194	=SUMPRODUCT(($A$5:$A$148=$A$176)*($E$4:$E$147=$A194)*($O$4:$O$147="MEPS"))
B197	B197	=IF(SUMPRODUCT(($A$5:$A$148=$A$176)*ISNUMBER($B$4:$B$147)*($E$4:$E$147=$A197)),"WALK-INS ARE NOT APPT","")