COUNTA

What it does

COUNTA(value1, [value2], ...)

COUNTA(value1, [value2], …) counts the arguments and scanned cells that are not empty.

The predicate is a single question asked of each value: is anything here? It is not "is this a number", not "does this coerce to a number", and not "does this display something". Microsoft's page states it as counting cells containing any type of information, "including error values and empty text" — and, explicitly, not counting truly empty cells.

That gives the following classification, which is the whole function:

Two consequences are worth stating because they are where readers are surprised.

COUNTA counts errors. It is the only common counting function that does. This is what makes COUNTA useful as a "how many cells have been filled in" measure and useless as a "how much good data do I have" measure. COUNT answers the second question.

COUNTA counts empty text. A cell holding =IF(A1>0,"x","") is counted whether or not the condition held, because the cell holds a formula and the formula published a text value — a zero-length one. This is the single most common cause of COUNTA disagreeing with what a human sees on the screen, and it is documented. Its mirror image is COUNTBLANK, which counts "" as blank; the two functions therefore both count the same cell, and COUNTA(r) + COUNTBLANK(r) exceeds the cell count of r by the number of empty-text cells. That is not a defect in either function — it is the documented consequence of two different definitions of "blank" living in one product.

Arguments

The commonly misunderstood position is that there isn't one — every position behaves identically. What does differ is how a value arrives. An omitted slot (COUNTA(1,,2)) delivers the Missing marker and contributes nothing; an empty cell reached through a range delivers Empty and likewise contributes nothing. The two are different kinds arriving by different routes at the same answer here (chapter 01), and COUNTA is one of the few functions where they agree.

Try it

Quirks

Result and edge cases

Returns Number — a non-negative integer count. It never returns an error on account of the data.

• COUNTA() of a range with nothing in it is 0, not an error.
• Direct arguments versus scanned cells. COUNTA("") and a cell containing "" are both counted, so unlike the aggregates COUNTA shows no direct-versus-scan asymmetry (chapter 02). That is a consequence of the predicate never attempting a coercion at all.

The value kind returned is always Number even when nothing was counted, so ISNUMBER(COUNTA(...)) is always TRUE and cannot be used as a guard.

Errors

COUNTA has no documented data-dependent error conditions — that is the point of it.

How to implement

Notes for implementers

2. Empty text is text. A zero-length string is a Text value and counts. This is the rule most often got wrong, and it must be decided at the value-kind level, not by asking whether the string renders as something.
3. Do not fold errors. An incoming error is a value to count, not a result to propagate. This is an explicit per-family exception to the universal error-propagation discipline (chapter 02) and needs to be declared, not inherited.
4. Distinguish Missing from Empty at the boundary even though they agree here. They agree for COUNTA and disagree for other members of the counting cluster; conflating them upstream makes the other functions unimplementable.

Rust code

aggregate(COUNTA, "COUNTA", VALUES, counta)

/// COUNTA: how many values there are. Every direct argument counts, an omitted one included; a
/// held value counts unless its cell is blank, so empty text and an error count. Counting needs
/// no rounding.
fn counta(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(COUNTA, services);
    let mut counted = 0u64;
    let read_through = read_through_held_arrays(arguments);
    let arguments: &[CalcValue] = read_through.as_deref().unwrap_or(arguments);
    let held = match read_held_references(&site, services, arguments, HeldReference::Counted) {
        Ok(held) => held,
        Err(exit) => return Site::finish(Err(exit)),
    };
    let arguments: &[CalcValue] = held.as_deref().unwrap_or(arguments);
    let read = each_value(&site, arguments, services, &mut |index, origin, value| {
        match (origin, value.core_ref()) {
            (_, CoreRef::Number(n)) => finite(&site, index, n)?,
            (Origin::Held | Origin::Element, CoreRef::Empty | CoreRef::Missing) => return Ok(()),
            (_, CoreRef::Array(_) | CoreRef::Reference(_)) => {
                return Err(site.not_modelled(Some(index)));
            }
            (_, CoreRef::Text(_) | CoreRef::Logical(_) | CoreRef::Error(_))
            | (Origin::Direct, CoreRef::Empty | CoreRef::Missing) => {}
        }
        counted += 1;
        Ok(())
    });
    Site::finish(read.and_then(|()| site.number(counted as f64)))
}

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

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• COUNT — the numeric-only counterpart, and the comparison Microsoft's own page draws: use COUNT if you do not need to count logical values, text, or error values. COUNT applies a coercion policy; COUNTA applies a classification. On a clean numeric column they agree; on any real column they do not, and the difference is the diagnostic.
• COUNTBLANK — the complement, except that it is not a complement. Both count a cell holding "". See the arithmetic note above.
• COUNTIF / COUNTIFS — the conditional counters Microsoft's page points to. Note that COUNTIF(range,"<>") is not a COUNTA substitute: it runs the criteria matcher with its own blank-handling rules rather than the non-emptiness predicate.
• ROWS/COLUMNS — the shape questions. ROWS(r)*COLUMNS(r) − COUNTA(r) counts truly empty cells and is not the same as COUNTBLANK(r), again because of empty text.
• Confused with: COUNTA as a data-quality measure. A column of #N/A is fully populated according to COUNTA.

Named variants

No named variants are recorded yet.