background
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
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for September 2024 is Open
Wall Street Prep

COUNTA Function

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

Last Updated December 6, 2023

Learn Online Now

COUNTA Function

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.

dl

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

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.

COUNTA Function Formula in Excel

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

COUNTA Excel Calculator

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.
Learn More
Comments
0 Comments
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.