Excel 2016 number of consecutive cells in row = 3

[Gregory123987]

After getting help with my previous post I now need to find an additional solution.

Spoiler: Reference

I have been searching for an example that can help me with what I am trying to do here. I want to find the number of consecutive populated cells from the right. If the cell is blank, then count the rest of the cells. I found several examples on finding consecutive cells, but none quite fit what I am looking for. I am not an advanced enough user to fully appreciate what you help me with. I would prefer a non-VBA solution to keep the workbook macro-free. Thank you for looking at my little problem.

This is a mock up showing what the answers should be to help to explain it.
The formula would go in the Result column and should have the range 1 to 31.
In row Person 1, the result should be 0 since column 31 is blank.
In row Person 2, the result should be 6 because there are 6 consecutive cells that are not blank.

I have another problem that is driving me nuts. This has to do with conditional formatting which makes things worse. I prefer to get the answer in a cell, which I can transfer to Conditional formatting. I have several conditional formatting in the rows. Please use Row 2 for the answer.

Here is what I need. When the row for person 1 has 3 consecutive cells in the row, it should highlight yellow. But it should do it only for the first occurrence. This is where I am really glad I have very short hair.

Cell N2 is where the highlighting should occur. The highlighting should not occur in Cells Q2, W2, AE2 as they are not the first occurrence. The problem I am having is that I can't find a way to identify it as the 2nd or greater occurrence so it does not highlight in cells Q2, W2, AE2 since it has already occurred in cell N2. Not without having an extremely long formula in cell AF2.

I do need to apply the Conditional formatting to all the cells in the row as the conditions for person 2 and 3 are equally possible. For the record, 0 would count as an occurrence.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1​	Day	1​	2​	3​	4​	5​	6​	7​	8​	9​	10​	11​	12​	13​	14​	15​	16​	17​	18​	19​	20​	21​	22​	23​	24​	25​	26​	27​	28​	29​	30​	31​
2​	Person 1											0​	5​	6​	1​	3​	0​				4​	0​	5​		1​	0​			5​	1​	6​
3​
4​	Person 2	0	2	1
5​
6​	Person 3																													1	1	5
7​

 

[KRice]

Would something like this work?

MrExcel_20200625.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Day	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
2	Person 1											0	5	6	1	3	0				4	0	5		1	0			5	1	6
3	Person 2	0	2	1
4	Person 3																													1	1	5
5	Person 4						0	2	4	4	2																			1	1	5
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AF5	Expression	=AND(COUNTA($B2:B2)=3,COUNTA($B2:B2)<>COUNTA(A2:$B2))	text	NO

 

[DanteAmor]

Hi @KRice, Check the following example. Your formula recognizes number 1, it should be number 3.

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Day	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
2	Person 1											2		6	1	3	0				4	0	5		1	0			5	1	6
Hoja2

Try this formula

=COLUMN()=(MIN(IF(($B2:$AD2<>"")*($C2:$AE2<>"")*($D2:$AF2<>""),COLUMN($B$1:$AD$1)+2)))

 

[KRice]

Hi @DanteAmor, thanks for catching that. I missed the detail about "three consecutive" possibly following something that COUNTA would count.

 

[Gregory123987]

I haven't tried it but I don't think what you explained matches what I am looking for. In my example, for Person 1, only the cell N2 should be highlighted.

Try this formula

=COLUMN()=(MIN(IF(($B2:$AD2<>"")*($C2:$AE2<>"")*($D2:$AF2<>""),COLUMN($B$1:$AD$1)+2)))

Thank you very much. This formula seems to work. I have even been able to adjust it to use for a 5th consecutive character, but can't seem to make it work for 10 consecutive chars...I get a #value error when I place it in a cell and try it. Any suggestions? I actually think I should tell you a little more about what I am trying to accomplish.
For the:
1st occurrence of a non-blank highlight red - No problem
3rd consecutive occurrence of a non-blank highlight Yellow - Thank you.
5th consecutive occurrence of a non-blank highlight Orange - Adjusted your answer to accomplish.

=COLUMN()=(MIN(IF(($B2:$AB2<>"")*($C2:$AC2<>"")*($D2:$AD2<>"")*($E2:$AE2<>"")*($F2:$AF2<>""),COLUMN($B$1:$AB$1)+4)))

10th consecutive occurrence of a non-blank highlight Blue - ??

=COLUMN()=(MIN(IF(($B2:$W2<>"")*($C2:$X2<>"")*($D2:$Y2<>"")*($E2:$Z2<>"")*($F2:$AA2<>"")*($G2:$AB2<>"")*($H2:$AC2<>"")*($I2:$AD2<>"")*($J2:$AE2<>"")*($K2:$AF2<>""),COLUMN($B$1:$AF$1)+9)))

 

[DanteAmor]

I get a #value error when I place it in a cell and try it

It is an array formula, to accept you must press Control + Shift + Enter

10th consecutive occurrence

Try:
=COLUMN()=(MIN(IF(($B2:$W2<>"")*($C2:$X2<>"")*($D2:$Y2<>"")*($E2:$Z2<>"")*($F2:$AA2<>"")*($G2:$AB2<>"")*($H2:$AC2<>"")*($I2:$AD2<>"")*($J2:$AE2<>"")*($K2:$AF2<>""),COLUMN($B$1:$W$1)+9)))

 

[Gregory123987]

Yes. It is an array formula for the cell, but the W did work. I feel a little silly for forgetting to adjust that as well. Thank you very much.

 

[DanteAmor]

Yes. It is an array formula for the cell, but the W did work. I feel a little silly for forgetting to adjust that as well. Thank you very much.

You do not feel bad about that, it is a long formula, any letter can fail.
Glad we could help & thanks for the feedback.

 

[Gregory123987]

Glad we could help & thanks for the feedback.

Thank you. I am glad you could help. The code for the conditional formatting was working very well until now. Of course the client has made just a teeny-tiny change (Their words). Of course I know that the change is not that teeny and this level of excel is mostly above me.

In addition to the current month, they want the formula to work along with the previous month. I have a formula to capture consecutive days from the end of the column, which I have linked to this page as a value, and now the conditional formatting need to use (Column J) as well which will have a numeric solution from 0 to a maximum of 25. I do hope you can help with this one more. If it would provide a simpler solution, I can move column J to column L. If the number in Column J is 7 then the last 7 days are consecutive. If the number of Column J is 0 then the last day was not present. For reference the formula that produces the value linked to column J is:

=IF(AO18<>0,($AI$15-(MAX(($M18:$AI18="")*($M$15:$AI$15)))),0)

A simple explanation is to use the number of consecutive days from the end of the previous month as well as the columns for the current month.

I have tried to provide as much information as possible. If you have any questions about this do not hesitate to let me know.

Notes:

The formulas in K7-K9 are placed in these cells for easy reference and are the conditional formulas currently being used and applies to cells M18 thru AI21 in the original spreadsheet These formulas do work as is in the conditional formatting.

Comments on the rows

PMCD is Prior Month Consecutive Days (Column J in table) and indicates the number of consecutive days backwards from the end of the prior month.
CD is Consecutive Days

1st 4 rows (18-21) are currently being used without using PMCD
2nd 4 rows (23-26) indicate what the result should be if using the column PMCD

Row 23: 3rd CD shouldn't apply since PMCD larger than 3
Row 26: 3rd CD shouldn't apply since PMCD larger than 3, and 5th CD shouldn't apply since PMCD is larger than 5

New formula testing with previous month consecutive days.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	Other stuff up here																				Other stuff over here
2
3	Jan 2020
4
5
6										Color Key
7										3rd CD	#####
8										5th CD	#####
9										10th CD	#####
10
11										CD = Consecutive Day
12
13
14	Other	Other	Fname	Lname	DOB	Eth	Other	Other	Other	PRIOR MONTH Consecutive Days	Other	Other	W	TH	F	S	T	W	TH	F	S	T	W	TH	F	S	T	W	TH	F	S	T	W	TH	F
15													1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23		Hidden Row for another purpose
16													1	2	3	4	7	8	9	10	11	14	15	16	17	18	21	22	23	24	25	28	29	30	31		Other
17	Separator
18			Person	1			0	0	0	0	0	0	1	2	0	1	4	3		1	1	2	5			3	2	0	0
19			Person	2			0	0	0	0	0	0	0	1	4	3		1	1	2	5			3	2
20			Person	3			0	0	0	0	0	0		3		1	1	2	5			3	2	0	1	0
21			Person	4			0	0	0	0	0	0	1	2	0	1	4	3	1	2	2	1	2	3		1	1	2	5			3	2	0	0
22
23			Person	1			0	0	0	3	0	0	1	2	0	1	4	3		1	1	2	5			3	2	0	0
24			Person	2			0	0	0	1	0	0	0	1	4	3		1	1	2	5			3	2
25			Person	3			0	0	0	0	0	0		3		1	1	2	5			3	2	0
26			Person	4			0	0	0	7	0	0	1	2	0	1	4	3	1	2	2	1	2	3		1	1	2	5			3	2	0	0
27
Sheet1

Cell Formulas
Range	Formula
K7	K7	=COLUMN()=(MIN(IF(($M18:$AG18<>"")*($M18:$AH18<>"")*($M18:$AI18<>""),COLUMN($M$16:$AG$16)+2)))
K8	K8	=COLUMN()=(MIN(IF(($M18:$AE18<>"")*($M18:$AF18<>"")*($M18:$AG18<>"")*($M18:$AH18<>"")*($M18:$AI18<>""),COLUMN($M16:$AE16)+4)))
K9	K9	=COLUMN()=(MIN(IF(($M18:$Z18<>"")*($M18:$AA18<>"")*($M18:$AB18<>"")*($M18:$AC18<>"")*($M18:$AD18<>"")*($M18:$AE18<>"")*($M18:$AF18<>"")*($M18:$AG18<>"")*($M18:$AH18<>"")*($M18:$AI18<>""),COLUMN($M$16:$Z$16)+9)))

 

[DanteAmor]

Of course I know that the change is not that teeny and this level of excel is mostly above me.

I think this level is also above me.
Let's hope if someone else can help us with the CF, otherwise could it be with a macro? you would have to press a button to highlight the cells.