How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But how do you calculate the size of each merged block in the adjacent column or row—without manually counting the cells? That’s exactly what this tutorial will cover.

Why You Should Be Cautious with Merged Cells

Before we dive in, a quick note: I generally don’t recommend merging cells in Google Sheets if you plan to filter, query, sort, or aggregate your data. Merged cells often disrupt these operations.

That said, merging cells can still be useful when designing clean templates—like test forms, invoices, or reports—where you don’t need advanced calculations.

Understanding How to Count Merged Cells

Let’s say you’ve merged the range A11:A15 in column A to display a bus route name. Now, you want to return the size of that merged block in B11. The result should be 5, because the merged block spans five rows.

You may have several such merged blocks in the sheet—either adjacent or separated by empty rows—and you want to calculate the size of each without hardcoding the ranges.

Unfortunately, Google Sheets doesn’t offer a built-in function to directly detect the size of merged cells. But the good news is, there’s a formula-based workaround that gets the job done.

Real-World Example: Bus Route Schedule

Let’s say you’re building a bus schedule where:

Column A lists route names.
Column B contains the assigned bus numbers.
The route names in column A are merged vertically based on how many buses are assigned.

Here’s what your data might look like:

Example showing the count of merged cells in a column in Google Sheets using a formula

Your goal is to count the number of merged cells in each block and display the result in column C, but only for the top row of each merged group.

How to Count Merged Cells in Google Sheets

Step-by-Step Instructions

Select the column containing merged cells (e.g., column A).
Click Format > Alignment > Center. This step is essential. The formula depends on text alignment to detect merged blocks. You can revert alignment later.
In cell C2, enter the following formula:

=ArrayFormula(
   LET(
      range, A2:A, 
      empty, MAP(range, LAMBDA(row, CELL("prefix", row))), 
      emptyf, IF(range<>"", range, empty), 
      process, SCAN(TOCOL(,3), 
      emptyf, LAMBDA(acc, val, IF(val="", acc, val))), 
      fnl, COUNTIF(process, range), 
      IF(fnl=0,, fnl)
   )
)

What This Formula Does

It returns the number of rows each merged block spans.
It places the result only in the first row of each merged group.
Ignores empty or unmerged rows when counting merged blocks.

You can adjust the formula for a specific block (e.g., A2:A4) if needed.

How the Formula Works

Let’s break it down:

Formula Logic

We’re using the CELL function to detect alignment, which changes based on whether the cell is merged.

Formula Breakdown

empty → MAP(range, LAMBDA(row, CELL("prefix", row)))

This uses CELL("prefix") to return a symbol (^) only in the top row of each merged block.

Identifying empty cells within merged blocks in Google Sheets

emptyf → IF(range<>"", range, empty)

Replaces the ^ symbol with the actual route name.

Preparing merged blocks in Google Sheets for filling down empty cells

process → SCAN(TOCOL(,3), emptyf, LAMBDA(acc, val, IF(val="", acc, val)))

Fills down the route name into the empty rows of each merged block.

fnl → COUNTIF(process, range)

Counts how many times each route appears in the filled range (i.e., how many rows the block spans).

IF(fnl=0,, fnl)

Hides zeros from empty cells or non-merged areas.

How to Count Merged Cells Across a Row

If your merged cells go horizontally across a row (instead of vertically), you can tweak the formula slightly.

For example, if the merged route names are in row 1 and the bus numbers are in row 2:

Place this in B3:

=ArrayFormula(
   LET(
      range, B1:1, 
      empty, MAP(range, LAMBDA(row, CELL("prefix", row))), 
      emptyf, IF(range<>"", range, empty), 
      process, SCAN(TOCOL(,3), 
      emptyf, LAMBDA(acc, val, IF(val="", acc, val))), 
      fnl, COUNTIF(process, range), 
      IF(fnl=0,, fnl)
   )
)

The only change is the reference from A2:A to B1:1.

Example showing the count of merged cells in a row in Google Sheets using a formula

FAQ

Q: Can I use this formula in Excel?
A: Unfortunately, no. This formula won’t work in Excel because the CELL("prefix") function behaves differently there. This method is specific to Google Sheets.

Q: Why center-align the cells?
A: The CELL("prefix") function returns alignment indicators (like ^ for center) only when the cell is visibly aligned (left, center, or right). Without this, the formula won’t accurately detect merged headers.

Q: Can I align the column after applying the formula?
A: No, you must apply the alignment (Center, Left, or Right) before entering the formula. The CELL("prefix") function only captures alignment status at the time the formula runs. Changing the alignment afterward won’t retroactively affect the result.

Q: Can I use this on partially filled merged ranges?
A: Yes. Even if some rows in a merged block are empty in adjacent columns, the formula still correctly measures the merged range using the alignment trick.