Excel: Count Blank Cells Between Data Rows in a Column
Question details
Find a formula that counts blank cells between populated rows in one Excel column.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has data in a single column and needs a running count of empty cells that occur before each subsequent populated row.
- Observed behavior
- The desired result is to display the number of blank cells between data rows while leaving blank source rows without a result.
Place the source values in column A, with the first data row starting in A1. Use an empty helper column, such as column B, for the running blank-cell count.
Use a helper formula to count blanks between populated rows
Use this method when you need the number of blank cells between each pair of populated rows in column A.
The formula returns a result only when the current cell in column A contains data. It calculates the blank cells above the current data row and subtracts counts already returned for earlier data rows.
Leave B1 empty or enter 0 in B1, then select cell B2 next to the second row of your data.
Type =IF(A2="","",COUNTBLANK(A$1:A1)-SUM(B$1:B1)) in B2 and press Enter.
Drag the fill handle from B2 down the helper column, or double-click the fill handle to copy the formula alongside the data range.
Check the values in column B where column A contains data. Each displayed number is the count of blank cells since the preceding populated row.
Count all blank cells in a selected Excel range
Use this method when you only need one total for all empty cells in a defined range rather than separate counts between data rows.
Count Blank Cells Between Data Rows with WPS Spreadsheets
WPS Spreadsheets supports COUNTBLANK and standard Excel-compatible formulas, so you can calculate blank cells between data rows with the same helper-column approach and fill the formula down quickly.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open your .xlsx or .xls workbook.
- 2. Choose a helper cell: Click B2, or the cell beside the second row of the source data in column A.
- 3. Enter the blank-count formula: Type =IF(A2="","",COUNTBLANK(A$1:A1)-SUM(B$1:B1)) and press Enter.
- 4. Copy the formula down: Select B2 and drag its fill handle downward to apply the formula to the remaining rows.
Frequently Asked Questions
What formula counts all blank cells in an Excel column?
Use COUNTBLANK with the required range, such as =COUNTBLANK(A:A) for the entire column or =COUNTBLANK(A1:A100) for a specific range.
How do I count only the blank rows between two entries in column A?
Put =IF(A2="","",COUNTBLANK(A$1:A1)-SUM(B$1:B1)) in B2 and fill it down. Results appear only on populated rows in column A.
Why does the helper formula return a blank cell instead of zero?
The IF(A2="","",...) portion intentionally returns an empty result when A2 is blank. This prevents every blank source row from showing a repeated calculation.
Does COUNTBLANK count cells that contain a formula returning an empty string?
Yes. COUNTBLANK treats cells containing formulas that return "" as blank. If you need to exclude those formula results, use a different condition-based formula.
