How to count empty cells in a row until encountering a filled cell in Google Sheets?

Other Questions Google Sheets
1

I’m building a scheduling system in Google Sheets where I need to calculate how many consecutive empty cells exist starting from a specific position. Each cell represents a 10-minute time slot, and I want to determine availability by counting empty cells until I hit one that contains data.

I have a formula that finds my starting position:

=REGEXREPLACE(ADDRESS(1,MATCH(FLOOR($A$2,"0:10"),E$1:CY$1,0)+4,4),"[1]",TEXT(ROW(),"0"))

This gives me the correct cell reference to begin from. Now I need to count consecutive blank cells moving right from this starting point, stopping as soon as I encounter any cell with content.

In my setup, empty cells mean the person is available, while filled cells indicate they’re occupied. I want to fit this entire logic into a single formula if possible so I can copy it down for multiple people in the schedule.

Is there a built-in function or formula approach that can count consecutive blanks starting from a dynamic cell position?

2

I’ve encountered this scheduling problem before. MATCH with IFERROR works great here. Try =IFERROR(MATCH(TRUE,INDIRECT(your_starting_cell&":"&SUBSTITUTE(your_starting_cell,COLUMN(INDIRECT(your_starting_cell)),COLUMN(INDIRECT(your_starting_cell))+50))<>"",0)-1,column_count) where column_count is your max range. MATCH finds the first non-empty cell, then subtracting 1 gives you the empty cell count before it. IFERROR catches cases where everything’s empty and returns your max count. Set a reasonable upper limit or it’ll bog down your sheet.

3

Try using SUMPRODUCT with an array formula. Here’s what works for me: =SUMPRODUCT(--(INDIRECT(your_starting_cell&":"&ADDRESS(ROW(INDIRECT(your_starting_cell)),COLUMN(INDIRECT(your_starting_cell))+20,4))="")*(SEQUENCE(1,21)<=MATCH(FALSE,INDIRECT(your_starting_cell&":"&ADDRESS(ROW(INDIRECT(your_starting_cell)),COLUMN(INDIRECT(your_starting_cell))+20,4))="",0))). SEQUENCE creates a counter that stops when MATCH hits the first filled cell. Change the 20 to whatever your max empty slots might be. Way cleaner than dealing with IFERROR, and you get better control over the counting.

4

try COUNTIF with INDIRECT to reference your dynamic starting position. something like =COUNTIF(INDIRECT(your_address&":"&SUBSTITUTE(your_address,COLUMN(INDIRECT(your_address)),COLUMN(INDIRECT(your_address))+10)),"") might work, but you’ll need to adjust the range logic. haven’t tested this exact syntax tho.