COUNTBLANK

What it does

COUNTBLANK(range)

COUNTBLANK(range) counts the cells in a range that are blank, where blank has a specific documented meaning that is not the same as "empty".

Microsoft's page states the rule in two sentences: cells with formulas that return "" (empty text) are also counted, and cells with zero values are not counted. So:

The definition is therefore display-shaped rather than content-shaped: a cell counts as blank if it shows nothing, even when it contains a formula. That is a defensible choice for the job the function exists to do — finding gaps in a data-entry region — and it is the reason COUNTBLANK is not the complement of COUNTA.

The arithmetic that surprises people. COUNTA counts a "" cell (it contains information) and COUNTBLANK counts the same cell (it displays nothing). So for a range r,

COUNTA(r) + COUNTBLANK(r)  ≥  ROWS(r) * COLUMNS(r)

with equality only when no cell holds empty text. Two functions, two definitions of blank, both documented, both correct on their own terms. Anyone reconciling a count should decide which definition they mean before choosing a function.

Arguments

COUNTBLANK(range) — one argument.

Microsoft's page states plainly that the function accepts one argument. The argument is a range, not a value: this is a reference-sensitive surface, and the distinction is load-bearing rather than stylistic — see the next section.

Try it

Quirks

The signature divergence

2. Excel accepts several ranges undocumented, and the documentation is narrow.
3. Excel accepts several arguments but the documentation describes the supported usage.

=COUNTBLANK(A1:A3, C1:C3) in a live Excel decides it in one cell. It is the first probe listed below, and it is the kind of question this Handbook exists to publish rather than guess at.

Result and edge cases

Returns Number — a non-negative integer count.

• Zero is not blank. Documented. This is the rule that makes COUNTBLANK usable on numeric columns where 0 is a real observation.
• Never-touched cells inside the range count. They have no value at all, which is the uncontroversial half of the definition.

Errors

The documentation states no error conditions for this function.

The array-rejection row has the live-Excel anchor described above. The error-propagation row has no anchor at all.

How to implement

Notes for implementers

1. Take the reference, not the values. Resolving to values first destroys the information the function needs: you cannot count cells that never existed as values. This is why the surface is declared reference-visible at the adapter.
2. Reject arrays deliberately. It is tempting to reuse the aggregate expansion helper that serves COUNT and COUNTA; that is precisely the mistake the upstream bug stream records, and the root-cause note calls it inheriting an over-permissive shape rather than a regression.
3. Empty text is a Text value. The predicate is "is this Text of zero length", not "does this render as nothing". A number formatted to display nothing is not blank.

Rust code

CatalogueEntry {
    id: COUNTBLANK,
    spelling: "COUNTBLANK",
    notation: Notation::Call,
    arity: CallableArityShape::exact(1),
    parameters: RANGE,
    demand: ArgumentDemand::Eager,
    kernel: Kernel::Eager(countblank),
}

/// COUNTBLANK: the cells of its reference, a single area, that are blank or hold empty text: the
/// area's size less the cells the reader visits with any other value, so the cost follows the
/// reader's visits and not the area's size, and a blank cell the reader visits as Empty counts
/// as blank. A reader that visits more such cells than the area holds breaks its contract and
/// cannot supply the area. A reference of several areas is #VALUE! (dc-bd5: COUNTBLANK((A5,A6))).
/// A computed scalar is #VALUE!; calc-fml lifts computed arrays over this scalar rule (W111
/// count-family and w089-axis-reference-vs-array-variant readings in OxFunc-Evidence).
fn countblank(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(COUNTBLANK, services);
    Site::finish(blanks(&site, &arguments[0], services))
}

fn blanks(
    site: &Site<'_>,
    argument: &CalcValue,
    services: &mut EvalServices<'_>,
) -> Step<CalcValue> {
    if argument.callable_value().is_some() {
        return Err(site.not_modelled(Some(0)));
    }
    let reference = match argument.core_ref() {
        CoreRef::Reference(reference) => reference,
        CoreRef::Error(code) => return Err(Exit::Error(code)),
        // Retained fn2 q187: an omitted optional LAMBDA parameter is #VALUE!.
        CoreRef::Missing => return Err(Exit::Error(WorksheetErrorCode::Value)),
        // Array evaluation belongs to calc-fml.
        CoreRef::Array(_) => {
            return Err(Exit::Fault(FunctionFault::ReferenceRequired {
                id: site.id(),
                index: 0,
            }));
        }
        _ => return Err(Exit::Error(WorksheetErrorCode::Value)),
    };
    let [area] = reference.areas() else {
        return Err(Exit::Error(WorksheetErrorCode::Value));
    };
    let cells = reader(site, services, 0)?;
    let mut held = 0u64;
    read_reference(site, cells, reference, 0, &mut |_, _, value| {
        let blank = match value.core_ref() {
            CoreRef::Empty | CoreRef::Missing => true,
            CoreRef::Text(text) => text.len_utf16_code_units() == 0,
            _ => false,
        };
        held += u64::from(!blank);
        Ok(())
    })?;
    let blank = area.cell_count().checked_sub(held).ok_or(Exit::Fault(
        FunctionFault::ReferenceUnreadable {
            id: site.id(),
            index: 0,
        },
    ))?;
    // At most 2^34 cells, which binary64 holds exactly.
    site.number(blank as f64)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• COUNTA — the near-complement that is not a complement, because both count empty-text cells. The pair is the clearest illustration in Excel that "blank" is not one concept.
• COUNT — counts numbers only; 0 counts there and does not count here, which is the one place all three functions agree in spirit.
• COUNTIF — COUNTIF(range,"") and COUNTIF(range,"<>") are the usual attempted substitutes. They run the criteria matcher's own blank-and-empty-text rules, which are a third definition again, so they are not reliable stand-ins. If you need a specific definition of blank, write it explicitly with ISBLANK or LEN.
• ISBLANK — the per-cell predicate, and a fourth definition: ISBLANK is false for a formula returning "", where COUNTBLANK counts it. SUMPRODUCT(--ISBLANK(range)) and COUNTBLANK(range) therefore disagree exactly on the empty-text cells, and that disagreement is the cleanest available diagnostic for how many such cells a range holds.
• AREAS, ISFORMULA, FORMULATEXT, SUBTOTAL, AGGREGATE — named in the upstream record as the same-direction reference-sensitive surfaces that also reject array substitutes. They are the family COUNTBLANK belongs to structurally, even though it is filed with the counting functions.

Named variants

No named variants are recorded yet.