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​

 

[Gregory123987]

It took me a while but I managed to figure out a solution. Here it for your info. Thanks for your help.

Added hidden columns,

Expanded conditional formatting to include applicable columns, wrapped Column with And and added cell reference such as the following to change from
=COLUMN()=(MIN(IF(($M18:$AG18<>"")*($M18:$AH18<>"")*($M18:$AI18<>""),COLUMN($M$16:$AG$16)+2))) to
=AND($J18<3,(COLUMN()=(MIN(IF(($T18:$AP18<>"")*($U18:$AQ18<>"")*($V18:$AR18<>""),COLUMN($T$16:$AP$16)+2)))))