| Function category | Statistical |
| Volatility | Non-volatile |
| Similar functions | COUNT |
| Syntax | COUNTA(value1, [value2], ...) |
What does the COUNTA function do?
The function counts the number of non-empty cells in one or more selected ranges.
COUNTA function syntax
The function syntax includes one required argument and additional optional arguments, up to 255 total. The argument can be a range of any size, a single cell or a value.
=COUNTA(value1,[value2],...)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data | What’s in the cell | Function | Result | |
| 2 | London | text | COUNTA | 6=COUNTA(A2:A8) | |
| 3 | 125 | number | COUNT | 3=COUNT(A2:A8) | |
| 4 | empty | COUNTBLANK | 2=COUNTBLANK(A2:A8) | ||
| 5 | 10/22/2026 | date | ROWS | 7=ROWS(A2:A8) | |
| 6 | #N/A | error | |||
| 7 | =IF(1>2,1,"") | formula returning "" | |||
| 8 | 0 | zero |
COUNTA counts everything except truly empty cells: text, numbers, dates (they are numbers), errors, zero and even a formula that returns an empty string (A7). COUNT counts only numbers and dates. COUNTBLANK counts A4 and A7 — so COUNTA + COUNTBLANK can exceed the number of cells.
Support for multiple arguments allows you to simultaneously check whether data is filled in multiple unrelated ranges, including on different sheets.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Plan | Actual | Filled | ||
| 2 | January | 120 | 6=COUNTA(A2:A5,C2:C5) | ||
| 3 | February | 95 | |||
| 4 | March | ||||
| 5 | 80 |
Arguments are separated by commas: A2:A5 has three values, C2:C5 also three, 6 in total. Ranges can be on different sheets: =COUNTA(Sheet1!A:A,Sheet2!A:A).
COUNTA: result and input format
The function accepts any input values, including calculation results, manually entered text, numeric and other values, data ranges of any size, including single cells. See examples in the header.
Counts cells containing text, even if the text string is empty ("").
Counts cells with error values (e.g., #DIV/0!, #N/A).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value | Type | How to count | Result | |
| 2 | London | text | COUNTA | 4=COUNTA(A2:A6) | |
| 3 | ="" | empty string "" | without "" | 3=COUNTIF(A2:A6,"?*")+COUNT(A2:A6)+SUMPRODUCT(--ISERROR(A2:A6)) | |
| 4 | #DIV/0!=1/0 | error | text and numbers only | 2=COUNTIF(A2:A6,"?*")+COUNT(A2:A6) | |
| 5 | 42 | number | |||
| 6 | empty |
If formulas in a column return "", COUNTA counts them as filled. The "?*" criterion in COUNTIF means text of at least one character, so empty strings are skipped; COUNT adds the numbers and ISERROR the errors.
Return value format is a positive integer or 0 (zero).
Examples of COUNTA usage
The function is useful as an auxiliary tool in situations where you need to determine whether a range contains any values or not.
Example 1. Removing empty rows
In short - empty rows often interfere with data analysis (read more about why it's important and how to remove empty rows in Excel using various methods). But deleting them isn't easy, especially if there are many.
A pragmatic solution is to use the COUNTA function in the first column to check rows for data. If the value is greater than zero, the row is not empty and shouldn't be deleted. And vice versa :)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Client | Phone | Amount | Filled | Empty? | |
| 2 | Acme Inc | (206) 555-0142 | 15000 | 3=COUNTA(A2:C2) | FALSE=COUNTA(A2:C2)=0 | |
| 3 | 0=COUNTA(A3:C3) | TRUE=COUNTA(A3:C3)=0 | ||||
| 4 | Globex | 8000 | 2=COUNTA(A4:C4) | FALSE=COUNTA(A4:C4)=0 | ||
| 5 | 0=COUNTA(A5:C5) | TRUE=COUNTA(A5:C5)=0 |
COUNTA over the whole row = 0 means the row is empty. Filter column F by TRUE and delete those rows.
Example 2. Employee salary calculation
Let's say the office has a device at the entrance that identifies employees by fingerprints. The COUNTA function in Excel can be used to count the number of days when an employee registered at the entrance. Here's how it might look:
- Each time an employee comes to work and passes fingerprint identification, the system registers their presence that day.
- Registration data is exported to Excel, where each row corresponds to an employee, and each column corresponds to a specific working day of the month. The employee's arrival time on a particular day is marked in the corresponding cell.
- The COUNTA function is used to count the number of days each employee was registered at work. For example, the formula
=COUNTA(B2:AF2)will count the number of filled cells in row 2 from B2 to AF2, which corresponds to the number of days the employee was at the workplace. - Using the counted number of working days, the accountant can calculate the employee's salary by multiplying this number by the daily pay rate.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | 10/01 | 10/02 | 10/03 | 10/04 | 10/05 | Days | Rate | Pay |
| 2 | Smith | 8:55 | 9:02 | 8:47 | 9:10 | 4=COUNTA(B2:F2) | 250 | 1000=G2*H2 | |
| 3 | Brown | 9:00 | 8:58 | 2=COUNTA(B3:F3) | 300 | 600=G3*H3 | |||
| 4 | Jones | 8:40 | 8:45 | 8:50 | 8:41 | 8:39 | 5=COUNTA(B4:F4) | 220 | 1100=G4*H4 |
A check-in time in a cell means the employee was at work. COUNTA across the row counts the days, and multiplying by the daily rate gives the pay.
This method allows the accountant to automate the process of counting working days and facilitates salary calculation based on employees' actual presence at work.
See also:
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.