Excel 2016 number of consecutive cells in row = 3

Gregory123987

New Member
Joined
Jun 23, 2020
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
After getting help with my previous post I now need to find an additional solution.
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.

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
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 2021
5
6
Person 3115
7
 
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, 1596916659204.png

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)))))
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top