COUNTBLANK function

COUNTBLANK returns the number of empty cells in a specified range, useful for identifying incomplete data or tracking active subscriptions.

=COUNTBLANK(range)

Generate a COUNTBLANK formula

Describe what you need. The generator will reach for COUNTBLANK where COUNTBLANK is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How COUNTBLANK reads its arguments
rangerequiredCOUNTBLANK →
ArgumentRequiredDescription
rangeRequiredA cell range (e.g., A1:A100). Must be a valid range reference; sparse/non-contiguous ranges require proper syntax in some sheets.

Returns

Returns an integer representing the count of empty cells.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count incomplete billing records

CustomerPlanMRRSignup DateChurn Date
AlicePro992024-01-15
BobBasic292024-02-012025-06-30
CharliePro992024-01-20
DianaEnterprise2992023-11-10
EmmaBasic292024-03-052025-08-15
FrankPro992024-04-01
GraceBasic2024-05-10
HenryPro992024-06-152025-09-01
=COUNTBLANK(C2:C9)

Result: 1

The MRR column (C2:C9) contains 8 cells, but only Grace's row (C8) is empty. COUNTBLANK returns 1, flagging that one billing rate is missing and needs to be completed before processing payment.

2. Identify active subscriptions

CustomerPlanMRRSignup DateChurn Date
AlicePro992024-01-15
BobBasic292024-02-012025-06-30
CharliePro992024-01-20
DianaEnterprise2992023-11-10
EmmaBasic292024-03-052025-08-15
FrankPro992024-04-01
GraceBasic492024-05-10
HenryPro992024-06-152025-09-01
=COUNTBLANK(E2:E9)

Result: 5

The Churn Date column (E2:E9) has 5 empty cells: Alice, Charlie, Diana, Frank, and Grace have no churn date, meaning they remain active subscribers and are still paying their monthly recurring revenue.

3. Audit data completeness across all fields

CustomerPlanMRRSignup DateChurn Date
AlicePro992024-01-15
BobBasic292024-02-012025-06-30
CharliePro992024-01-20
DianaEnterprise2992023-11-10
EmmaBasic292024-03-052025-08-15
FrankPro992024-04-01
GraceBasic2024-05-10
HenryPro992024-06-152025-09-01
=COUNTBLANK(B2:E9)

Result: 6

Across the 4-column data range (B2:E9, 32 cells total), COUNTBLANK finds 6 blanks: 5 in the Churn Date column representing active customers, and 1 in MRR (Grace's missing rate). This holistic scan reveals data quality issues across the entire table.

Common errors

Which COUNTBLANK error are you seeing?
COUNTBLANK returned an error#NAME?
Verify the function is spelled exactly COUNTBLANK with no extra letters or characters.
#REF!
Re-enter the formula using a valid, existing range like =COUNTBLANK(A1:A100), or re-define the deleted named range.
#VALUE!
Ensure the argument is a proper cell range reference (A1:A10) or named range, not a literal string or number.
ErrorWhy it happensHow to fix it
#NAME?The function name is misspelled, e.g., =COUNTBLNK(A1:A100) or =COUNTBLANKS(A1:A100).Verify the function is spelled exactly COUNTBLANK with no extra letters or characters.
#REF!The range reference points to cells or a named range that has been deleted, moved, or is otherwise no longer valid.Re-enter the formula using a valid, existing range like =COUNTBLANK(A1:A100), or re-define the deleted named range.
#VALUE!The argument is not a valid range—for example, passing a text string directly (=COUNTBLANK("text")) or using malformed range syntax.Ensure the argument is a proper cell range reference (A1:A10) or named range, not a literal string or number.

Tips and when to use something else

  • COUNTBLANK counts only completely empty cells. Cells containing a space, 0, or a formula that returns an empty string (like =IF(A1="", "", A1)) are not counted as blank.
  • To count non-empty cells instead of empty ones, use COUNTA(range). To count cells that meet a specific condition, use COUNTIF or COUNTIFS.
  • Combine COUNTBLANK with ROWS to calculate completeness: =1-(COUNTBLANK(A1:A100)/ROWS(A1:A100)) returns the fraction of the range that is populated.

Related functions

COUNTACOUNTA counts the number of non-empty cells in a range, regardless of their content type or whether they contain text, numbers, or errors.COUNTCOUNT counts the number of cells containing numeric values and dates in one or more ranges, ignoring text and blank cells.COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.COUNTIFSCOUNTIFS counts rows where all criteria are met across multiple ranges, useful for filtering inventory and finding matches across conditions.

Frequently asked questions

Does COUNTBLANK count cells with spaces or formulas that return empty strings?
No. COUNTBLANK only counts completely empty cells. A cell with a space, a 0, or a formula like =IF(false,"","") is not counted as blank.
How do I count active subscriptions where the Churn Date field is empty?
Use COUNTBLANK(E2:E100) where column E contains churn dates. It returns the count of customers with no churn date—those still active and generating recurring revenue.
Can COUNTBLANK work on non-contiguous ranges like A1:A10 and C1:C10 together?
In Google Sheets, use =COUNTBLANK(A1:A10,C1:C10). In Excel, use the same comma-separated syntax. Some older Excel versions may require Union syntax: =COUNTBLANK((A1:A10,C1:C10)).
How does COUNTBLANK differ from COUNTA?
COUNTBLANK counts empty cells, while COUNTA counts non-empty cells. Together, they partition the range: COUNTBLANK(A1:A100) + COUNTA(A1:A100) equals the total number of rows (100).

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17