Skip to content

Excel function · Statistics

Excel COUNTA function

COUNTA counts every cell in a range that is not empty: text, numbers, dates, logical values or errors alike. It is used to count the records in a list or the fields filled in on a form.

In Turkish Excel: BAĞ_DEĞ_DOLU_SAY

What it does

For COUNTA it does not matter what a cell contains; if it is not empty, it counts. =COUNTA(A2:A500) returns the number of rows whose name column is filled, which is the number of records in the list.

Watch out for two traps. A cell holding only a space looks empty but is not, so it counts. A formula that returns empty text, such as ="", also makes the cell non-empty. If the result is larger than you expect, look for these two cases first.

To count only numbers use COUNT; to count empty cells, COUNTBLANK; to count a particular value, COUNTIF. In Microsoft 365, the number of distinct values in a column is =COUNTA(UNIQUE(B2:B500)).

Syntax

English Excel=COUNTA(value1, [value2], ...)
Turkish Excel=BAĞ_DEĞ_DOLU_SAY(değer1; [değer2]; ...)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
value1RequiredThe first range, cell or value whose non-empty cells are counted.
value2OptionalOptional further ranges or values, up to 255 arguments.

Examples

1Number of records and filled-in fields

F2 counts the records with a company name and G2 those with an email address. The columns hold text, so COUNT would return 0 here.

F2fx=COUNTA(A2:A6)
Number of records and filled-in fields: example table
ABCDEFG
1CompanyContactEmailRecordsWith Email
2Brown & SonsEmma Brownsales@brownandsons.co.uk53
3Northgate LtdLucas Gray
4Harbour Tradeinfo@harbourtrade.co.uk
5Star MarketAva Thompson
6Oak BuildersNoah Clarksupport@oakbuilders.com
  • F2=COUNTA(A2:A6)
  • G2=COUNTA(C2:C6)

The list has 5 companies; 3 of them have an email address.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2How many questions did each survey answer?

Answers can be text or numbers. The formula in F2 is filled down and counts how many of the four questions were answered on each row.

F2fx=COUNTA(B2:E2)
How many questions did each survey answer?: example table
ABCDEF
1SurveySatisfactionScoreRecommendCommentAnswered
2A-01Satisfied9YesFast delivery4
3A-02Neutral62
4A-038Yes2
5A-04Unsatisfied3NoParcel arrived damaged4
  • F2 (filled down to F5)=COUNTA(B2:E2)

The first and last surveys answered all 4 questions; A-02 answered only 2.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3How many different customers placed orders?

The same customer placed several orders. UNIQUE in Microsoft 365 lists each customer once, and COUNTA counts that list.

D2fx=COUNTA(B2:B7)
How many different customers placed orders?: example table
ABCDE
1OrderCustomerOrdersCustomers
2S-1001Brown & Sons63
3S-1002Northgate Ltd
4S-1003Brown & Sons
5S-1004Harbour Trade
6S-1005Northgate Ltd
7S-1006Brown & Sons
  • D2=COUNTA(B2:B7)
  • E2=COUNTA(UNIQUE(B2:B7))

6 orders came from 3 different customers.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

Count too high

Cause: Some cells that look empty contain a space or a formula returning empty text; they count as non-empty.

Fix: To count only cells that really hold something, use =SUMPRODUCT(--(TRIM(A2:A500)<>"")).

One too many

Cause: The range includes the header row (A1:A500), and the header is a non-empty cell too.

Fix: Start the range at the first data row: A2:A500.

Good to know

  • To see how many fields of a form are filled in on each row, use =COUNTA(B2:F2) and fill it down.
  • To find out whether numbers are stored as text, compare COUNTA with COUNT on the same range.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a summary showing how many customers are in the list and how many have an email address

Convert to table

Bring your customer list into the tool and type this. Nexell builds the counts with real Excel formulas and shows them in the preview first; in the downloaded file, the counts update as you add information.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I count non-empty cells in Excel?

=COUNTA(A2:A500) counts the cells that are not empty. To count only numbers, use COUNT.

Why does COUNTA count cells that look empty?

Those cells contain a space or a formula that returns empty text, and Excel treats them as non-empty. Select the cell and look at the formula bar, or check it with =LEN(A2).

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free