Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Statistical

COUNT

AANTAL in Dutch Excel

Counts how many cells in the arguments hold a number, including dates and zeros, and skips text, empty cells and errors.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	Debtor	Confirmed balance	Replies received
2	Bakker Staalbouw BV	€12,400.00€ 12.400,00	44=COUNT(B2:B7)=COUNT(B2:B7)
3	Yilmaz Elektro BV
4	Raman Logistics BV	€8,750.50€ 8.750,50
5	De Vries Isolatie BV	€0.00€ 0,00
6	Jansen Bouwmarkt BV
7	Smeets Dakbedekking BV	€3,120.00€ 3.120,00

Result: 4

=COUNT(B2:B7)

=COUNT(B2:B7)

What COUNT does and when to use it

What it does: it counts how many cells in a range hold a number, for example how many debtors have confirmed a balance.

When to use it: to check how many amounts or dates a column really holds, especially in imported data. For filled cells of any kind use COUNTA, for a condition COUNTIF or COUNTIFS.

How Excel evaluates it

Excel takes the arguments from left to right. For a range it visits every cell: a number, date or time adds 1, while text, an empty cell, TRUE or FALSE and an error value add nothing.

Values typed straight into the formula follow a looser rule: numbers, dates, TRUE and FALSE and text such as "5" all count, but the same text sitting in a cell does not. The result is the total of those 1s, a whole number that is 0 when nothing qualifies.

Watch out: COUNT skips numbers stored as text and gives no warning, so the result can be lower than the number of rows.

Mental model

Picture a clerk with a tally counter who clicks only for numbers. Text gets no click, however numeric it looks, and neither does an empty cell.

Terms used on this page: error value, argument, range, cell

Syntax

Official COUNT documentation

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

COUNT(value1; [value2]; ...)

Arguments
Argument	Type	Meaning
`value1`	Required	A cell, range or value; only numbers are counted.
`value2`	Optional	More ranges or values, up to 255 arguments in total.

Return value:The number of numeric values found in the arguments

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 debtor confirmations received

You sent balance confirmation requests to six debtors of Veldhuis Installatie BV. In your tracking sheet you type the confirmed balance in column B when a reply arrives and leave the cell empty until then. You want to see how many replies you have so far, including a debtor who confirmed a nil balance.

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	Debtor	Confirmed balance	Replies received
2	Bakker Staalbouw BV	€12,400.00€ 12.400,00	44=COUNT(B2:B7)=COUNT(B2:B7)
3	Yilmaz Elektro BV
4	Raman Logistics BV	€8,750.50€ 8.750,50
5	De Vries Isolatie BV	€0.00€ 0,00
6	Jansen Bouwmarkt BV
7	Smeets Dakbedekking BV	€3,120.00€ 3.120,00
8

Formula cell (D2)Input

Formula in D2

=COUNT(B2:B7)

=COUNT(B2:B7)

Result: 44

Step by step

Excel reads the range B2:B7 and visits the cells from the top. B2 holds the number 12,40012.400, so the count becomes 1.
B3 is empty. An empty cell is not a number, so nothing is added and the count stays 1.
B4 holds 8,750.508.750,50: the count becomes 2. B5 holds 0, the nil balance confirmed by De Vries Isolatie BV. Zero is a number, so the count becomes 3.
B6 is empty again, so the count stays 3. B7 holds 3,1203.120, so the count becomes 4.
The result in D2 is 4: four of the six debtors have replied. Type 5,2005.200 in B3 and the result becomes 5, without touching the formula.

Watch out: Type 0 for debtors you are still waiting for and COUNT reports them as replies. Leave the cell empty until the confirmation arrives.

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

Why it works

COUNT looks at what a cell holds, not at what it shows. A confirmed balance is a number, so it counts, including 0; a debtor who has not replied leaves the cell empty, so it does not. That makes the formula a dependable response counter, as long as you keep empty cells empty.

More to watch out for

A balance typed as text, for example with a space in front of it, is not counted and the debtor looks silent. Check for left-aligned entries in column B.

Variations

=ROWS(B2:B7) - COUNT(B2:B7)

=ROWS(B2:B7) - COUNT(B2:B7)

Shows how many debtors you still have to chase (2). Prefer it when the open items matter more than the replies.

Intermediate

Find imported amounts that Excel does not read as numbers

The client delivered the purchase ledger of Veldhuis Installatie BV as a CSV file. You pasted the amount column into your workpaper and the total looks too low.

Before looking for the cause you want one figure: how many filled cells in the amount column are not numbers. COUNTA counts the filled cells, COUNT counts how many of them Excel reads as numbers, and the difference is your answer.

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	Invoice no.	Amount (import)	Amounts stored as text
2	P-25-1001	€4,250.00€ 4.250,00	33=COUNTA(B2:B9) - COUNT(B2:B9)=COUNTA(B2:B9) - COUNT(B2:B9)
3	P-25-1002	1875.50
4	P-25-1003	€960.00€ 960,00
5	P-25-1004	12300.00
6	P-25-1005
7	P-25-1006	€2,480.75€ 2.480,75
8	P-25-1007	530.00
9	P-25-1008	€7,115.00€ 7.115,00
10

Formula cell (D2)InputNote: B3, B5 and B8 hold text, not numbers: they are left-aligned and show a green triangle. B6 is empty. The other amounts are real numbers.

Formula in D2

=COUNTA(B2:B9) - COUNT(B2:B9)

=COUNTA(B2:B9) - COUNT(B2:B9)

Result: 33

Step by step

Excel evaluates COUNTA(B2:B9) first. It counts every cell that is not empty: B2 to B5 are filled (4), B6 is empty, B7 to B9 are filled (3). The result is 7.
Then COUNT(B2:B9) visits the same cells. B2 holds 4,2504.250: count 1. B3 holds the text 1875.50, which is not a number: skipped. B4 holds 960: count 2.
B5 holds the text 12300.00: skipped. B6 is empty: skipped. B7 holds 2,480.752.480,75: count 3. B8 holds the text 530.00: skipped. B9 holds 7,1157.115: count 4. The result is 4.
Excel subtracts: 7 - 4 = 3. Three filled cells are not numbers: B3, B5 and B8.
Those three hold 1,875.501.875,50 + 12,300.0012.300,00 + 530.00 = 14,705.5014.705,50 that SUM would leave out. Convert the text and D2 drops to 0, so the formula also works as a check.

Watch out: The formula gives a how many, not a which. Find the cells with a filter on text, or look for the green triangles, before you convert anything.

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

Why it works

COUNTA counts a cell because something is in it; COUNT counts it only if that something is a number. The difference is exactly the set of cells that are filled but not numeric. The empty cell B6 appears in neither count, so it does not distort the result.

More to watch out for

A formula that returns empty text ("") counts as filled for COUNTA and is not a number for COUNT, so it shows up in the result even though the cell looks empty.

Variations

=SUMPRODUCT(--ISTEXT(B2:B9))

=SUMPRODUCT(--ISTEXT(B2:B9))

Counts only cells that hold text (3). Prefer it when error values may sit in the column: COUNTA counts them, so the main formula would include them.

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

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

Counts every row that is not a number, empty rows included (4). Prefer it when an empty amount is also a finding.

Advanced

Test whether every bank reconciliation is filled in

Veldhuis Installatie BV has four bank accounts and prepares a reconciliation every month. The sheet holds the unexplained difference per account for September to November. Each cell means one of four things:

0: reconciled
a number other than 0: a difference to follow up
text: the reconciliation is in progress
an empty cell: nothing was prepared

You want one status cell that says whether all 12 figures are in.

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F
1	Bank account	Sep	Oct	Nov	Status
2	Operating account	€0.00€ 0,00	€0.00€ 0,00	€0.00€ 0,00	Incomplete=IF(COUNT(B2:D5) = ROWS(B2:D5) * COLUMNS(B2:D5), "Complete", "Incomplete")=IF(COUNT(B2:D5) = ROWS(B2:D5) * COLUMNS(B2:D5); "Complete"; "Incomplete")
3	Savings account	€0.00€ 0,00	€0.00€ 0,00	pending
4	Payroll account	€0.00€ 0,00	-€45.50€ -45,50
5	Currency account	€12.80€ 12,80	€0.00€ 0,00	€0.00€ 0,00
6

Formula cell (F2)Input

Formula in F2

=IF(COUNT(B2:D5) = ROWS(B2:D5) * COLUMNS(B2:D5), "Complete", "Incomplete")

=IF(COUNT(B2:D5) = ROWS(B2:D5) * COLUMNS(B2:D5); "Complete"; "Incomplete")

Result: Incomplete

Step by step

Excel evaluates the inner functions first. ROWS(B2:D5) returns 4 and COLUMNS(B2:D5) returns 3.
The multiplication gives 4 * 3 = 12, the number of cells that should hold a difference.
COUNT(B2:D5) goes through the block row by row:
- operating account: 0, 0, 0 gives 3 numbers (zero counts)
- savings account: 0, 0 and the text pending gives 2
- payroll account: 0, -45.50 and an empty cell gives 2
- currency account: 12.80, 0, 0 gives 3
The total is 3 + 2 + 2 + 3 = 10. Excel compares 10 = 12, which is FALSE.
IF therefore returns its third argument: Incomplete appears in F2.
Enter a number in D3 and in D4 and COUNT rises to 12, the comparison becomes TRUE and F2 shows Complete.

Watch out: Silent failure: someone types 0 in a cell to make the status turn to Complete without preparing the reconciliation. COUNT cannot tell a real 0 from a placeholder, so review the sign-off, not only the count.

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

Why it works

The expected count comes from the shape of the block, so adding a row or column to the range changes both sides of the comparison and nothing is hard-coded. COUNT accepts only numbers, so text and empty cells both fail the test, while 0 passes, which is what you want for a reconciliation without difference.

More to watch out for

A figure entered as text, such as 12,80 typed with a stray space, does not count, and the status stays Incomplete although the cell looks filled.
The test checks that figures exist, not that they are small. Payroll account October holds -45.50, which is counted as present and still needs follow-up.

Variations

=ROWS(B2:D5) * COLUMNS(B2:D5) - COUNT(B2:D5)

=ROWS(B2:D5) * COLUMNS(B2:D5) - COUNT(B2:D5)

Returns the number of missing figures (2) instead of a verdict. Prefer it when you report how much is outstanding.

=COUNT(B2:D2)

=COUNT(B2:D2)

Counts the figures in one row (3 for the operating account). Copy it down to see which account is incomplete.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Count weekend postings in manual journal entries

For journal entry testing on Veldhuis Installatie BV you look for risk indicators in the December postings. One of them is a manual entry (journal MEM) posted on a Saturday or Sunday.

The condition is computed from the date, so a plain COUNTIFS cannot express it. COUNT can, once IF has kept only the dates that pass the test.

Sheet excerpt: F2 holds the formula
Row	A	B	C	E	F
1	Entry no.	Journal	Posting date	Journal	MEM
2	2025-0412	MEM	27 Dec 2025	Weekend postings	33=LET( postDates, C2:C9, isWeekend, WEEKDAY(postDates, 2) > 5, isManual, B2:B9 = F1, COUNT(IF(isWeekend * isManual, postDates)) )=LET( postDates; C2:C9; isWeekend; WEEKDAY(postDates; 2) > 5; isManual; B2:B9 = F1; COUNT(IF(isWeekend * isManual; postDates)) )
3	2025-0413	VRK	28 Dec 2025
4	2025-0414	MEM	29 Dec 2025
5	2025-0415	MEM	28 Dec 2025
6	2025-0416	INK	20 Dec 2025
7	2025-0417	MEM	31 Dec 2025
8	2025-0418	MEM	13 Dec 2025
9	2025-0419	BNK	15 Dec 2025
10

Formula cell (F2)InputNote: Column C holds real dates. 27 December 2025 is a Saturday, 28 December a Sunday, 13 and 20 December Saturdays; 15 and 29 December are Mondays, 31 December a Wednesday.

Formula in F2

=LET(
  postDates, C2:C9,
  isWeekend, WEEKDAY(postDates, 2) > 5,
  isManual, B2:B9 = F1,
  COUNT(IF(isWeekend * isManual, postDates))
)

=LET(
  postDates; C2:C9;
  isWeekend; WEEKDAY(postDates; 2) > 5;
  isManual; B2:B9 = F1;
  COUNT(IF(isWeekend * isManual; postDates))
)

Result: 33

Step by step

LET names postDates as C2:C9, eight dates stored as serial numbers. Nothing is counted yet.
isWeekend: WEEKDAY with return_type 2 numbers the days Monday = 1 to Sunday = 7, giving {6; 7; 1; 7; 6; 3; 6; 1}. The test > 5 keeps Saturday and Sunday: {TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.
isManual: B2:B9 = F1 compares every journal with MEM and gives {TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}.
Multiplying turns TRUE and FALSE into 1 and 0. The product is {1; 0; 0; 1; 0; 0; 1; 0}: only entries 2025-0412, 2025-0415 and 2025-0418 pass both tests.
IF has no third argument, so a 1 returns the posting date and a 0 returns FALSE: {date; FALSE; FALSE; date; FALSE; FALSE; date; FALSE}.
COUNT counts the 3 dates and skips the FALSE values, because logical values inside an array are not numbers. The result is 3. Change F1 to VRK and it becomes 1 (entry 2025-0413, a Sunday).

Watch out: Silent mistake: WEEKDAY(postDates) without return_type 2 uses Sunday = 1 to Saturday = 7, so > 5 selects Friday and Saturday. The formula still returns a plausible number, only the wrong one.

More on this example: why it works, 3 more pitfalls, 2 variations

Why it works

IF without a third argument acts as a filter: it keeps the dates that pass and replaces the rest with FALSE, which COUNT ignores inside an array. The condition can therefore be any computed test, here the weekday, which COUNTIFS cannot evaluate because its criteria compare the cell value itself.

More to watch out for

A blank posting date is read as serial number 0, which WEEKDAY places on a Saturday, so a blank date on a manual entry is counted as a weekend posting. Test for blanks separately.
A posting date stored as text is skipped by COUNT without any error. IF returns the text itself and COUNT does not count text, so a weekend posting with a text date is missing and the result is too low. This holds whether or not WEEKDAY can read the text. Confirm with COUNT(C2:C9) that all 8 dates are real dates.
LET needs Excel 2021 or Microsoft 365; older versions do not have LET at all, so this formula cannot be used there. Use the SUMPRODUCT variation in a workbook that others open in an older version.

Variations

=SUMPRODUCT((WEEKDAY(C2:C9, 2) > 5) * (B2:B9 = F1))

=SUMPRODUCT((WEEKDAY(C2:C9; 2) > 5) * (B2:B9 = F1))

Gives 3 in every Excel version without array entry. Prefer it when others open the file in an older version.

Unlike COUNT(IF(...)), it does not need the date cell to be a number: a text date that WEEKDAY can still read is counted. A blank date (serial 0, a Saturday) is counted by both formulas, as the pitfalls say. The two formulas can therefore differ for text dates, so check the dates with COUNT(C2:C9) first.

=COUNTIFS(B2:B9, F1)

=COUNTIFS(B2:B9; F1)

Counts all MEM lines (5). When the condition is a plain value in a column, COUNTIFS is shorter and needs no array evaluation: do not build COUNT(IF(...)) for that.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Count lower than the number of rows	The result is lower than the number of rows because some amounts were imported as text. They are left-aligned and often carry a green triangle.	Compare with COUNTA to see how many cells are filled but not numbers, then convert the text with VALUE or Text to Columns.
Names or codes missing from the count	COUNT is used to count filled cells, and text entries such as names or codes are missing from the result.	COUNT counts numbers only. Use COUNTA for 'not empty' and keep COUNT for amounts, quantities and dates.
Placeholder zeros counted as work done	A 0 typed as a placeholder for 'not done yet' is counted, so the sheet reports work as finished.	A 0 is a number. Leave a cell empty until there is a result, or use text such as pending, which COUNT skips.
0 from COUNT(B2:B9>1000)	COUNT(B2:B9>1000) returns 0. The comparison produces TRUE and FALSE values, and COUNT does not count those.	Count by a condition with COUNTIF(B2:B9, ">1000"), or let IF return the numbers that pass, as in the Master example.