google-sheets

Is there a google sheets function to count blank cells and stop when it reaches a full cell?

I'm working on a schedule in google sheets that counts available cells to cleanly display availability. Each cell represents a 10-minute interval. I have a cell where a time can be input, and I have a function that gets that time, rounds it down, and returns the start position for the function. I'm really trying to get this function to fit in one cell so that I can have a one cell function for each person on the schedule. Is there a function where I could use a cell position as a starting point and count to the right, stopping when it reaches a full cell?

My current function looks like this:

=REGEXREPLACE(ADDRESS(1,MATCH(FLOOR($B$1,"010"),D$1:CX$1,0)+3,4),"[1]",TEXT(ROW(),"0"))

It returns a cell, which is exactly what I want. Now I just need to find a way to start counting from this cell value and stop when it reaches a full cell. Empty cells represent availability, while cells with a value in it represent being busy.

TLDR, I need a function that counts blank cells to the right and stops when it reaches a full cell.

Image of my project so far

Solution

  • Use match() to find the index of a cell that matches a value, like this:

    =max(0, match("*", D4:4, 0) - match(B$1, D$1:$1, 1))

    screenshot

    For row 1, use search_type 1 to find the first cell where the value is less than or equal to the search key in cell B1.

    For the current row, use search_key * that will match any text string, and search_type 0 to find the first non-blank cell.

    Then just subtract the former index from the latter to get the difference. To ensure that the result is always at least zero, use the max(0, ...) pattern.

    The formula uses open-ended references like D4:4 so that it will continue working regardless of how many columns you add.

    The formula assumes that the values in cell D4 down and to the right are text strings. To make it also work with numbers, and to mask errors when the row is blank, use this longer formula:

    =ifna(max(0, match("?*", sort(to_text(D4:4)), 0) - match(B$1, D$1:$1, 1)))

    The sort(to_text()) pattern converts numbers to text, the ?* pattern means "any text string that has at least one character," and ifna() masks errors.

    See match(), max(), to_text() and ifna().