Counting Blank Cells

[Reymets10]

In row 3 are dates, from 11/25/18 starting in B3 to today 8/27/19 in JQ3. There will be numbers being entered below the dates...say for row 4. I’m looking to count blank spaces between the numbers that may appear in the cells in row 4. For every 42 blank cells that is the equivalent to 1. The minimum has to be at least 42 to count and it can go into multiples of 42. So at 84 there is another point so that will be 2. Every 42 days would be 1 point. But the streak can be broken then the blank cells would need to be another 42 days to equal 1 point.

It should never go over 6 points total and is limited to check 275 days. 1 new column would be added everyday with a new date.

I hope I explained it well. My company doesn’t allow me to access this site for some reason so I’m posting this from my cell. I hope it makes sense.

Thank you for your time.

 

[James006]

Hello,

Your explanation is not extremely clear ...

Have you tried : =COUNTBLANK (your range)

 

[Reymets10]

Yes. I don’t want every blank cell counted. Only the blank cells within the data that are at least 42 blank spaces long. Every time there is data in a cell in that row the blank spaces count again to try to get to 42.

 

[Phuoc]

Try this:

=SUM(INT(FREQUENCY(COLUMN($B3:$JQ$4),IF($B3:$JQ$4<>"",COLUMN($B3:$JQ$4)))/42))

Array formula, enter with Ctrl+Shift+Enter.

 

[Reymets10]

Try this:

=SUM(INT(FREQUENCY(COLUMN($B3:$JQ$4),IF($B3:$JQ$4<>"",COLUMN($B3:$JQ$4)))/42))

Array formula, enter with Ctrl+Shift+Enter.

Thank you for the help. I am getting a #NAME ? Error

 

[Reymets10]

Try this:

=SUM(INT(FREQUENCY(COLUMN($B3:$JQ$4),IF($B3:$JQ$4<>"",COLUMN($B3:$JQ$4)))/42))

Array formula, enter with Ctrl+Shift+Enter.

Thank you for the help. I am getting a #NAME ? Error