What is the Excel COUNTA Function?
The COUNTA Function in Excel counts the number of cells that are not blank, such as those containing numbers, text, dates, and other values.
How to Use COUNTA Function in Excel?
The COUNTA function is a built-in feature in Excel that returns the number of non-blank cells in a selected range.
For instance, the COUNTA function could be used to count the number of respondents from a survey or the total number of dates given a large data set.
The most common examples of items counted by the function include:
- Numbers (e.g. Hard-Coded Inputs and Calculations)
- Text
- Percentages
- Dates
- Logical Values
- Cell References
- Special Values (e.g. Zip Code)
The COUNTA function counts all cells containing any sort of value in the selected range, such as those showing error values and empty text.
- Error Value → An error message is displayed in Excel once a problem is identified in which the calculation cannot be completed (e.g. “”).
- Empty Value → An empty value can result from number formatting in which the value of zero is set to appear as a blank space (e.g. “”).
The accidental inclusion of an error message should be relatively easy to avoid, considering how visible the error messages are.
However, certain cells can often appear blank yet contain a hidden figure (and thus still be counted under the COUNTA function). To ensure the cells meant to be blank are in fact treated as blank, use the following steps to select all the blank cells in the sheet:
- Step 1 → Open “Go To” Box (F5)
- Step 2 → Click “Special”
- Step 3 → Select “Blanks”
Excel COUNTA Function Formula
The Excel COUNTA function formula is as follows.
The bracket around “value2” and all the subsequent entries denote that those inputs are optional and can be omitted.
- Minimum Number → The selected range must have a minimum of one value.
- Maximum Number → On the other hand, the cap for the maximum number of arguments is 255.
COUNTA Excel Function Syntax
The table below describes the syntax of the Excel COUNTA function in more detail.
| Argument | Description | Required? |
|---|---|---|
| “value1” |
|
|
| “value2” |
|
|
COUNTA Function Calculator – Excel Model Template
We’ll now move on to a modeling exercise, which you can access by filling out the form below.
Excel COUNTA Function Calculation Example
Suppose you’re tasked with counting the number of employees that worked over the holidays.
Using the following data set – which states the hours logged per employee – the total number of employees working per day must be calculated.
Of the ten employees at this particular company, half of the employees are currently on paid time off (PTO) for the holidays.
| Hours Logged | 12/24/22 | 12/25/22 | 12/30/22 | 12/31/22 | 01/01/23 |
|---|---|---|---|---|---|
| Employee 1 | 4 | 2 | 4 | 2 | 6 |
| Employee 2 | 8 | 10 | 8 | ||
| Employee 3 | |||||
| Employee 4 | 6 | 8 | 6 | ||
| Employee 5 | |||||
| Employee 6 | 4 | 6 | 4 | ||
| Employee 7 | |||||
| Employee 8 | |||||
| Employee 9 | |||||
| Employee 10 | 12 | 10 | 12 | 10 | 12 |
Once the data is entered into Excel, the COUNTA function can be used to determine the number of employees working each day.
Note that if the empty cells contained either “0” or “N/A”, those would still be mistakenly counted.
We’re left with the following figures for the working employee count per day.
- 12/24/22 = 5 Employees
- 12/25/22 = 2 Employees
- 12/30/22 = 5 Employees
- 12/31/22 = 2 Employees
- 01/01/23 = 5 Employees
