Count the number of blank cells in a range, starting and ending will cells that match string values

[JaredXIII]

Greetings MrExcel forum! First time poster here, reaching out because I'm getting desperate to determine if there is a solution to my problem.

I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.

I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?

The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.

Thanks so much for your help!!

 

[ChrisR]

Hi,

try this

=COUNTBLANK(INDIRECT(ADDRESS(MATCH("Clock In",B:B,0),2)):INDIRECT(ADDRESS(MATCH("Clock out",B:B,0),2)))

 

[JaredXIII]

Excellent! This worked perfectly; all I had to do was add 1 to the count, since it won't count the "Clock in" cell as production time (I knew this would happen,though), and multiply the total by 0.25

=((COUNTBLANK(INDIRECT(ADDRESS(MATCH("Clock In",B:B,0),2)):INDIRECT(ADDRESS(MATCH("Clock out",B:B,0),2)))+1)*0.25) Gives me a correct count of 7.

Thanks so much for the fast response, you are awesome!

 

[ChrisR]

Excellent, glad it worked for you.

Thank you for the feedback.

 

[Scott Huish]

=(COUNTBLANK(INDEX(B:B,MATCH("Clock In",B:B,0)):INDEX(B:B,MATCH("Clock Out",B:B,0)))+1)*0.25

 

[JaredXIII]

This worked perfectly, also, and it gives me some insight on different ways of getting this done. Thanks very much for taking the time to reply!