How Can I Count Instances of "x" Consecutive Blank Cells in a Given Row

I am creating a spreadsheet that tracks when specific cleaning tasks are performed. The spreadsheet will have columns numbered 1-365 corresponding to each day of the year (1=January 1st). Each task will have a specific frequency for which it should be performed. The employee will input their initials in the cell corresponding to the task and date that they complete a task. Shown below.

		1	2	3	4	5	6	7	8	9	10
Area/Location	Frequency
West Cooler	Weekly		SS							SS

I need a way to keep running counts of the amount of times that the task was completed on time and the amount of times that it was not. I will use these counts to calculate on-time completion percentages for given tasks over the course of the months/year.

My initial thought is to create a formula that counts the number of blank cells between two cells containing values, in a given row. Using a weekly frequency for example, any instance of >6 consecutive blank cells between 2 cells containing initials indicates that the task was not completed on time. Is there a way to do this such that the formula scans an entire row and returns a count of the total number of instances where there were >6 consecutive blank cells? Likewise, I would need to keep a running total of the number of instances where there were <=6 blank cells between two cells containing initials (task completed on time). All help is much appreciated!

#VALUE! error for the logical tests on the if functions. I think it is because you cannot use a range for the test. It also looks like your two if functions located inside the frequency function return the same value, COLUMN(B3:AU3), so I am not sure how that is supposed to distinguish between >6 and <=6 values.

Yes. When I copy and paste it into my spreadsheet it shows a value of 0. But when I substitute in my range, it shows a value error.

When the formula is array entered and formula evaluated. You will see Logical test portion return array of TRUE/FALSE.

This...

#VALUE! error for the logical tests on the if functions.

And this...

But when I substitute in my range, it shows a value error.

Tells me that, you are not correctly confirming array.

Follow these steps.

1. Double click on a cell, paste in formula.
2. Replace range with your range. But do not leave the cell or hit enter yet.
3. While still in edit mode, hit CTRT + SHIFT + ENTER key.

You should see {} (curly brackets) surrounding the formula when the formula is correctly confirmed as array formula.

Yep, I forgot to array enter again after I changed the range. Sorry about that. It seems to be working! Thank you.
Can you please explain to me the role of the 2 if statements within the frequency function? To me it looks like they both have the same return if true and combined they cover all possible ranges, so I am not understanding.

Also, here is my last question. My range is 1-365 days, so until I enter a value within 6 days of the 365th day, there is always going to be a long string of blank cells that will add 1 to the calculated total of >6 consecutive blank cells. Is there any way to negate this without subtracting 1 from the equation? I am making the spreadsheet for all of 2017 and I might forget to go back remove the "- 1" at the end of the year.

Here, have a read of the link on Frequency function (especially about data_array & bin_array)
http://www.excelfunctions.net/Excel-...-Function.html

I'll see if I have time to write up more detailed explanation of the entire formula tomorrow.

Is there any way to negate this without subtracting 1 from the equation?

There are ways, but dependent on your set up.
I'd imagine that you can check for last non-empty cell in a given row, and use that as condition for subtracting 1.

Assuming that you have 1~365 in Row1 and checking for Row3...
=LOOKUP(2,1/(C3:NC3<>""),$C$1:$NC$1)

Will return value of Row1 that corresponds to last non-empty cell in Row3.

Explanation of previous formula to come later.

Thank you for the awesome explanation and response time. I love seeing the creative ways that these functions can be used to solve problems. You were very helpful. This was my first time using these forums and it definitely won't be the last.