Google Sheets: Count Cells Between First and Last Non-Blanks in a Row

You can use two XMATCH functions to count the number of cells between the first and last non-blank cells in a row in Google Sheets — both inclusive.

Table of Contents

Formula to Count Cells Between First and Last Non-Blanks in a Row

=ArrayFormula(
   IFNA(
      XMATCH(TRUE, NOT(ISBLANK(row)), 0, -1) - 
      XMATCH(TRUE, NOT(ISBLANK(row)), 0, 1) + 1
   )
)

Here, row is the horizontal range (e.g., B2:H2) where you want to count the number of non-blank cells from the first to the last, including any blank cells between them.

Example

Assume you have an employee timesheet in the range A1:H, with working hours logged across weekdays:

Employee	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Emily		8	8	7			4
Michael	8	8	8	8	8

You want to calculate the total span of days each employee was active, from their first entry to the last, including any off-days in between.

For Emily:

First non-blank cell: Tuesday (8)
Last non-blank cell: Sunday (4)
Count of cells (Tue to Sun): 6 days

You can use the following formula in cell I2 and drag it down:

=ArrayFormula(
   IFNA(
      XMATCH(TRUE, NOT(ISBLANK(B2:H2)), 0, -1) - 
      XMATCH(TRUE, NOT(ISBLANK(B2:H2)), 0, 1) + 1
   )
)

Google Sheets demo: Dragging formula to count cells between first and last non-blank entries in each row

Formula Breakdown

1. NOT(ISBLANK(B2:H2)) – returns TRUE for each non-empty cell.

FALSE

TRUE

TRUE

FALSE

TRUE

2. Both XMATCH functions use this logical array to find positions:

3. The first XMATCH (search from end) returns the last non-blank cell.

4. The second XMATCH (search from start) returns the first non-blank cell.

Subtracting the two and adding 1 gives the total count between them, inclusive.

Count Cells Between First and Last Non-Blanks in a Row – Spill Formula

To apply this formula to multiple rows without dragging down manually, use the BYROW function:

=BYROW(B2:H, LAMBDA(row, 
   ARRAYFORMULA(
      IFNA(
         XMATCH(TRUE, NOT(ISBLANK(row)), 0, -1) - 
         XMATCH(TRUE, NOT(ISBLANK(row)), 0, 1) + 1
      )
   )
))

Explanation:

LAMBDA(row, ...) defines a custom function for each row.
BYROW applies this function row by row over the range.
Inside the lambda, we’re just using the same formula from earlier.

Tip: Count Cells Between First and Last Non-Blanks in a Column

Want to do the same for vertical data? Just change the range to a column (e.g., B2:B20):

=ArrayFormula(
   IFNA(
      XMATCH(TRUE, NOT(ISBLANK(B2:B20)), 0, -1) - 
      XMATCH(TRUE, NOT(ISBLANK(B2:B20)), 0, 1) + 1
   )
)

To apply this across multiple columns, use the BYCOL function:

=BYCOL(B2:H10, LAMBDA(col, 
   ARRAYFORMULA(
      IFNA(
         XMATCH(TRUE, NOT(ISBLANK(col)), 0, -1) - 
         XMATCH(TRUE, NOT(ISBLANK(col)), 0, 1) + 1
      )
   )
))

Note: You can rename the lambda variable to col for better readability in column operations.

Why Count Cells Between First and Last Non-Blanks in Google Sheets?

Use this technique to:

Measure the active period in timesheets or logs.
Analyze user engagement in row-wise data.
Clean data with gaps while preserving the entry range.
Automate row-wise or column-wise span calculations without scripting.