All versionsExamples need Excel 2021 or laterStatistical

COUNTA

AANTALARG in Dutch Excel

Counts the cells in one or more ranges that hold anything at all: a number, text, a date, TRUE or FALSE, or an error.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDE
1LocationItemCounted by
2A-01Copper pipe 22 mmTBTags completed44=COUNTA(C2:C8)=COUNTA(C2:C8)
3A-02Cable NYM 3x2.5
4B-01Thermostatic valveAY
5B-02Circuit breaker 16 APR
6C-01Heat pump outdoor unit
7C-02Pipe insulationTB
8C-03Distribution board

Result: 4

=COUNTA(C2:C8)
=COUNTA(C2:C8)

What COUNTA does and when to use it

What it does: it counts the cells in a range that are not empty, whatever they hold, for example how many stock count tags have been returned.

When to use it: for completeness checks, such as suppliers with an IBAN or rows in an export. It is also the function that counts text, which COUNT skips. For real numbers only use COUNT, for a condition COUNTIF or COUNTIFS.

How Excel evaluates it

Excel goes through the arguments from left to right. For a range, it checks every cell and adds 1 when the cell is not truly empty; the type of the content is irrelevant.

For a value typed in the formula or produced by a calculation, it adds 1 for each value. The final total is the number of non-empty cells or values.

Watch out: COUNTA tests whether anything is stored in a cell, not whether it looks empty: a cell that holds a space, or a formula that returned "", is counted.

Mental model

A clerk checking a stack of forms for any ink at all. A signature, a scribble or a stray dot makes the form count; only a form with no mark whatsoever is skipped.

Terms used on this page: argument, range, cell

Syntax

Official COUNTA documentation
COUNTA(value1, [value2], ...)
COUNTA(value1; [value2]; ...)
ArgumentTypeMeaning
value1RequiredA range or value; anything non-empty counts.
value2OptionalMore ranges or values.

Return value:A whole number: how many cells or values are not empty

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Count the stock count tags that are filled in

You attend the year-end stock count at the warehouse of Veldhuis Installatie BV. Each of the 7 locations has a count tag, and the counter writes initials in the sheet when the tag is done. You want one cell that shows how many tags are completed, so you can see how far the count is.

ABCDEF
1LocationItemCounted by
2A-01Copper pipe 22 mmTBTags completed44=COUNTA(C2:C8)=COUNTA(C2:C8)
3A-02Cable NYM 3x2.5
4B-01Thermostatic valveAY
5B-02Circuit breaker 16 APR
6C-01Heat pump outdoor unit
7C-02Pipe insulationTB
8C-03Distribution board
9
Formula cell (E2)Input

Formula in E2

=COUNTA(C2:C8)
=COUNTA(C2:C8)

Result: 44

Step by step
  1. Excel takes the range C2:C8, seven cells in the column Counted by, and checks each one for content.

  2. The cells one by one:

    • C2 holds TB (counts)
    • C3 is empty (skipped)
    • C4 holds AY (counts)
    • C5 holds PR (counts)
    • C6 is empty (skipped)
    • C7 holds TB (counts)
    • C8 is empty (skipped)
  3. Four cells hold something: 1 + 1 + 1 + 1 = 4, so COUNTA returns 4. Three tags are still open.
  4. Once the counter of A-02 types initials in C3, the result changes to 5 without touching the formula.

Watch out: If a counter clears a cell by typing a space instead of pressing Delete, the tag counts as done. Check suspicious cells with LEN.

More on this example: why it works

Why it works

COUNTA only asks whether a cell holds anything, so initials, a number or a date would count the same. That makes it the right function for text, where COUNT would return 0 for these three-letter initials.

Intermediate

Completeness of IBAN and VAT number per supplier field

Before relying on the supplier master file for the payment run test, you check how complete it is. Some suppliers lack an IBAN, others lack a VAT number. You want the share of filled cells per column, in one formula that you write under the IBAN column and copy to the right.

ABCD
1SupplierIBANVAT no.
2Hofman Kabelhandel BVNL12BANK0123456701NL812345678B01
3Van der Linden InstallatiemateriaalNL12BANK0123456702
4Kaya Elektro GroothandelNL12BANK0123456703NL812345679B01
5Tielemans Montage BVNL812345680B01
6Raman KlimaattechniekNL12BANK0123456705NL812345681B01
7De Bruin StaalhandelNL12BANK0123456706
8Oosterhuis LogistiekNL12BANK0123456707NL812345683B01
9Mulder GereedschappenNL12BANK0123456708NL812345684B01
10
11Completeness87.5%87,5%=COUNTA(B2:B9) / COUNTA($A$2:$A$9)=COUNTA(B2:B9) / COUNTA($A$2:$A$9)
12
Formula cell (B11)InputNote: The formula is in B11. Copy it to C11 to get the same check for column C.

Formula in B11

=COUNTA(B2:B9) / COUNTA($A$2:$A$9)
=COUNTA(B2:B9) / COUNTA($A$2:$A$9)

Result: 87.5%87,5%

Step by step
  1. Excel evaluates the first COUNTA over B2:B9. Seven IBANs are present and B5 (Tielemans Montage BV) is empty, so the result is 7.
  2. It then evaluates COUNTA($A$2:$A$9) over the supplier names. All 8 cells are filled, so the result is 8. This is the number of suppliers.
  3. The division 7 / 8 = 0.875. With the percent format the cell shows 87.5%.
  4. Copied to C11, the relative reference B2:B9 becomes C2:C9 and $A$2:$A$9 stays. C3 and C7 are empty, so 6 VAT numbers are present and 6 / 8 = 0.75, shown as 75%.
  5. Without the dollar signs the denominator would shift to COUNTA(B2:B9) in C11, and the result would be 6 / 7 = 85.7%, a number that looks plausible and is wrong.

Watch out: A cell with a single space instead of an IBAN is counted as filled and the completeness is overstated. COUNTA cannot see it.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The supplier name column is complete by design, so counting it gives the number of rows without a fixed number in the formula. Locking that range with $ lets one formula serve every column, while the numerator range moves with each copy.

More to watch out for

  • Filled is not the same as valid. A cell with 'n.v.t.' counts as an IBAN. Completeness is only the first check, followed by a format check.

Variations

=1 - COUNTBLANK(B2:B9) / ROWS(B2:B9)
=1 - COUNTBLANK(B2:B9) / ROWS(B2:B9)

The same percentage from the other side: COUNTBLANK counts empty cells and also cells with "" from a formula. Prefer it when the column is filled by formulas.

Advanced

Journal entries really approved, ignoring spaces

In the journal entries of Veldhuis Installatie BV, every manual entry should carry the initials of an approver. In the export, entries without approval show an empty cell, but some users typed a space to 'clear' the field, which leaves it looking empty. You want the number of entries that have a real approver.

ABCDEF
1Entry no.Approved byApproved entries44=COUNTA(B2:B9) - SUMPRODUCT(--(B2:B9 <> ""), --(TRIM(B2:B9) = ""))=COUNTA(B2:B9) - SUMPRODUCT(--(B2:B9 <> ""); --(TRIM(B2:B9) = ""))
225-1101SvdV
325-1102
425-1103
525-1104AY
625-1105TB
725-1106
825-1107
925-1108PR
10
Formula cell (E1)InputMarks: extra spaceNote: B4 and B7 hold a single space. A cell that holds "" from a formula is counted by COUNTA but is not caught by this formula; the first variation handles it.

Formula in E1

=COUNTA(B2:B9) - SUMPRODUCT(--(B2:B9 <> ""), --(TRIM(B2:B9) = ""))
=COUNTA(B2:B9) - SUMPRODUCT(--(B2:B9 <> ""); --(TRIM(B2:B9) = ""))

Result: 44

Step by step
  1. First COUNTA(B2:B9). It counts every cell that is not truly empty: SvdV, the space in B4, AY, TB, the space in B7 and PR. That is 6, and it would suggest that only 2 of the 8 entries lack approval.
  2. Now the correction, inside SUMPRODUCT. B2:B9 <> "" is TRUE for every cell that is not truly empty: {TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}. The double minus makes it {1; 0; 1; 1; 1; 1; 0; 1}.
  3. TRIM(B2:B9) strips the spaces from each cell, so the spaces in B4 and B7 and the empty cells B3 and B8 all become empty text. TRIM(B2:B9) = "" gives {FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE}, and the double minus makes it {0; 1; 1; 0; 0; 1; 1; 0}.
  4. SUMPRODUCT multiplies the two arrays position by position: {0; 0; 1; 0; 0; 1; 0; 0}. Only the cells that are filled but hold nothing but spaces remain: B4 and B7. The sum is 2.
  5. COUNTA minus SUMPRODUCT is 6 - 2 = 4. Four of the 8 entries have an approver, so 4 are unapproved, twice what COUNTA alone suggested.

Watch out: Silently wrong: a non-breaking space (character 160), common in web and system exports, survives TRIM and is counted as an approver. Use TRIM(SUBSTITUTE(B2:B9, CHAR(160), " ")) in place of TRIM(B2:B9).

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

COUNTA gives the headline figure but counts whatever is stored, spaces included. The SUMPRODUCT part finds the cells that are stored yet hold only spaces, the ones COUNTA should not have counted, and removes exactly those. Cells that are truly empty are in neither count, so nothing is subtracted twice.

More to watch out for

  • A cell that holds 'n.v.t.' or 'x' is a real value and counts as approved. The formula tests for content, not for valid initials.

Variations

=SUMPRODUCT(--(TRIM(B2:B9) <> ""))
=SUMPRODUCT(--(TRIM(B2:B9) <> ""))

Without COUNTA: count the cells whose trimmed text is not empty. It also copes with "" from formulas, which the main formula does not subtract.

=ROWS(B2:B9) - COUNTBLANK(B2:B9)
=ROWS(B2:B9) - COUNTBLANK(B2:B9)

Handles "" from formulas, because COUNTBLANK treats those as blank, but not typed spaces: this data would give 6, not 4.

MasterExcel 2021 or later

Missing invoice numbers in a sequence, with duplicates and blanks

For the completeness of revenue you test the sales invoice numbering of Veldhuis Installatie BV. The control record says the series runs from V-2601 to V-2609.

The export has duplicates, one with a trailing space, and a row where the invoice number is empty. You want to know how many numbers in the series are missing.

ABCDEF
1Invoice no.AmountFirst number26012601
2V-2601€1,200.00€ 1.200,00Last number26092609
3V-2602€845.50€ 845,50Missing33=LET( ids, TRIM(A2:A9), seen, UNIQUE(FILTER(ids, ids <> "")), E2 - E1 + 1 - COUNTA(seen) )=LET( ids; TRIM(A2:A9); seen; UNIQUE(FILTER(ids; ids <> "")); E2 - E1 + 1 - COUNTA(seen) )
4V-2602€845.50€ 845,50
5V-2604€3,100.00€ 3.100,00
6V-2605€560.00€ 560,00
7€2,250.00€ 2.250,00
8V-2608€1,780.00€ 1.780,00
9V-2609€920.00€ 920,00
10
Formula cell (E3)InputMarks: extra spaceNote: A4 holds V-2602 followed by a space. The invoice number in A7 is empty but the row has an amount of 2,250.00, which is a finding by itself.

Formula in E3

=LET(
  ids, TRIM(A2:A9),
  seen, UNIQUE(FILTER(ids, ids <> "")),
  E2 - E1 + 1 - COUNTA(seen)
)
=LET(
  ids; TRIM(A2:A9);
  seen; UNIQUE(FILTER(ids; ids <> ""));
  E2 - E1 + 1 - COUNTA(seen)
)

Result: 33

Step by step
  1. LET stores ids as TRIM(A2:A9): {V-2601; V-2602; V-2602; V-2604; V-2605; ""; V-2608; V-2609}. TRIM removes the trailing space in A4, so it equals A3, and the empty A7 becomes "".
  2. FILTER(ids, ids <> "") keeps the 7 items that are not empty text and drops the "" from A7.
  3. UNIQUE removes the second V-2602. seen becomes {V-2601; V-2602; V-2604; V-2605; V-2608; V-2609}, 6 distinct numbers.
  4. COUNTA(seen) counts the 6 items, all text.
  5. The series holds E2 - E1 + 1 = 2609 - 2601 + 1 = 9 numbers. 9 - 6 = 3, so three numbers are missing: V-2603, V-2606 and V-2607.
  6. The opposite mistake: leave out FILTER and UNIQUE(ids) keeps the "" as an item, so seen has 7 entries and COUNTA counts the empty text as one. The result would be 9 - 7 = 2, one gap hidden.

Watch out: A related mistake: =E2 - E1 + 1 - COUNTA(UNIQUE(A2:A9)) looks like the same test and gives 9 - 8 = 1 on this data. The trailing space makes V-2602 count twice and the empty cell counts once.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

The series is text, so COUNT would return 0 here and COUNTA is the function that counts the distinct numbers. ROWS(seen) would give the same 6 on this data; COUNTA is used because it counts items rather than positions, so it also works if seen is later replaced by a range with truly empty cells or by a row instead of a column.

The two clean-up steps matter because COUNTA counts whatever it is given: a "" or a number with a trailing space adds 1, the same as a real invoice number does.

Do not use this construction on a population of hundreds of thousands of lines or when you also need to list the gaps: then use SEQUENCE to build the expected numbers and check each with COUNTIF, or run a gap test in Power Query or your data analysis tool. The trade-off is more calculation, in exchange for a list of exactly which numbers are missing.

The formula needs LET, FILTER and UNIQUE, which exist in Excel 2021 and Microsoft 365; older versions do not have LET at all.

More to watch out for

  • Silently wrong: if the export holds a number outside the series, such as V-2700, it is counted as seen and hides a gap. Check the minimum and maximum of the numbers against E1 and E2 first.
  • If every cell in A2:A9 is empty, FILTER returns an error and the formula fails. In that case you do not have an export, which is the finding.

Variations

=SUMPRODUCT(--(COUNTIF(A2:A9, "V-" & SEQUENCE(E2 - E1 + 1, 1, E1)) = 0))
=SUMPRODUCT(--(COUNTIF(A2:A9; "V-" & SEQUENCE(E2 - E1 + 1; 1; E1)) = 0))

Builds the nine expected numbers with SEQUENCE and counts the ones COUNTIF does not find, so it returns a count (3 here), not a list. It needs no removal of duplicates, but COUNTIF compares the whole cell: a number that only occurs with a trailing space is not found and would count as missing, so clean the column first.

To list the gaps, wrap the same test in FILTER.

Common mistakes

SymptomCauseFix
Half-empty column gives a high countA column that looks half empty gives a high count, because the 'empty' cells hold a space, an apostrophe or a formula that returns "".Test with SUMPRODUCT(--(LEN(TRIM(range)) > 0)), which counts only cells with visible text, or use ROWS(range) - COUNTBLANK(range) when the culprit is "" from formulas.
Text, TRUE or errors counted as numbersCOUNTA is used to count numbers, and a text cell, a TRUE or an error in the range is counted as well.Use COUNT when only real numbers should count. COUNTA is for 'filled in', COUNT is for 'is a number'.
Count is 1 or 2 too highThe range includes the header row or a total row, so the result is 1 or 2 too high.Start the range under the header and stop above the total row, or subtract 1 for a header on purpose and say so in a label.
1 instead of 0 for an empty FILTERCOUNTA(FILTER(...)) returns 1 instead of 0 when the filter finds nothing, because the error or the empty text that FILTER returns is itself a value.Test the condition first, for example with SUM(--(condition)), and only count the filtered result when that is above 0.