Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for September 2024 for September 2024
:
Private EquityReal Estate Investing
Wharton & Wall Street Prep Certificates:
Enrollment for September 2024 is Open

# COUNTA Function

Step-by-Step Guide to Understanding the "COUNTA" Function in Excel

Last Updated December 6, 2023

## 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.

=COUNTA(value1, [value2], …)

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
• The argument containing a value such as a number, text, or date that fulfills the criteria of minimum one value.
• Required
value2
• The additional arguments in the selected range of values that the COUNTA function is counting.
• Optional

## 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.

Submitting...

## 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

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.