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.

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:

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
   )
)

Formula Breakdown

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

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.

Related Resources

• COUNTIF Across Columns Row by Row – Array Formula in Google Sheets
• Get the Count of Occurrences in Each Row in Google Sheets
• Count Rows and Entries Between Two Values in Google Sheets
• Row-Wise COUNTUNIQUEIFS in Google Sheets (Array Formula)
• Count Empty or Blank Cells in Rows in Google Sheets