COUNTA Function in Microsoft Excel
Part 1: Introduction
Definition
The COUNTA function in Microsoft Excel is a handy tool that counts the number of cells that are not empty in a given range.
Purpose
The primary purpose of the COUNTA function is to count cells containing any type of information, including error values and empty text (“”). For example, if a range has a formula that returns an empty string, the COUNTA function counts that value. It’s important to note that the COUNTA function does not count empty cells.
Syntax & Arguments
The syntax for the COUNTA function is as follows:
COUNTA(value1, [value2], ...)
Here’s a breakdown of the arguments:
value1: This is the required first argument representing the values you want to count.value2, ...: These are optional additional arguments representing the values you want to count, up to 255 arguments.
Return Value
The COUNTA function returns the count of cells that are not empty in a range.
Remarks
If you do not need to count logical values, text, or error values (in other words, if you want to count only cells that contain numbers), use the COUNT function. If you count only cells that meet specific criteria, use the COUNTIF or COUNTIFS function.
Part 2: Examples
Let’s look at examples of using the COUNTA function in business.
Example 1
Purpose of Example: To count the number of employees who have submitted their timesheets.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Employee ID | Timesheet |
| 2 | E001 | Submitted |
| 3 | E002 | |
| 4 | E003 | Submitted |
| 5 | COUNTA | =COUNTA(B2:B4) |
Explanation: In this example, we have a list of employees and whether they have submitted their timesheets. The formula =COUNTA(B2:B4) counts the number of cells in column B that are not empty, giving us the number of employees who have submitted their timesheets. The result is 2.
Example 2
Purpose of Example: To count the number of products that have a listed price.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Product ID | Price |
| 2 | P001 | $20 |
| 3 | P002 | |
| 4 | P003 | $30 |
| 5 | COUNTA | =COUNTA(B2:B4) |
Explanation: In this example, we list products and their prices. The formula =COUNTA(B2:B4) counts the number of cells in column B that are not empty, giving us the number of products that have a listed price. The result is 2.
Example 3
Purpose of Example: To count the number of sales transactions that have been completed.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Transaction | Status |
| 2 | T001 | Complete |
| 3 | T002 | Pending |
| 4 | T003 | Complete |
| 5 | COUNTA | =COUNTA(B2:B4) |
Explanation: In this example, we have a list of sales transactions and their statuses. The formula =COUNTA(B2:B4) counts the number of cells in column B that are not empty, giving us the number of transactions that have a status (in this case, “Complete” or “Pending”). The result is 3.
Example 4
Purpose of Example: To count the number of customers who have provided their email addresses.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Customer | Email Address |
| 2 | C001 | test1@test.com |
| 3 | C002 | |
| 4 | C003 | test3@test.com |
| 5 | COUNTA | =COUNTA(B2:B4) |
Explanation: In this example, we have a list of customers and their email addresses. The formula =COUNTA(B2:B4) counts the number of cells in column B that are not empty, giving us the number of customers who have provided their email addresses. The result is 2.
Example 5
Purpose of Example: Count the number of projects with an assigned project manager.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Project | Project Manager |
| 2 | P001 | John Doe |
| 3 | P002 | |
| 4 | P003 | Jane Doe |
| 5 | COUNTA | =COUNTA(B2:B4) |
Explanation: In this example, we have a list of projects and their assigned project managers. The formula =COUNTA(B2:B4) counts the number of cells in column B that are not empty, giving us the number of projects that have an assigned project manager. The result is 2.
Example 6
Purpose of Example: To count the number of employees who have submitted their timesheets and whose hours are over 40.
Data Tables and Formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee ID | Timesheet | Hours | Count |
| 2 | E001 | Submitted | 45 | |
| 3 | E002 | 38 | ||
| 4 | E003 | Submitted | 42 | |
| 5 | COUNTA | =COUNTA(IF(B2:B4=”Submitted”,IF(C2:C4>40, C2:C4))) |
Explanation: In this example, we have a list of employees, whether they have submitted their timesheets and the number of hours they have worked. The formula =COUNTA(IF(B2:B4="Submitted",IF(C2:C4>40, C2:C4))) counts the number of cells where the timesheet is presented and the hours are over 40. The result is 2.
Example 7
Purpose of Example: To count the number of products with a listed price over $30.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Product ID | Price | Count |
| 2 | P001 | $20 | |
| 3 | P002 | ||
| 4 | P003 | $35 | |
| 5 | COUNTA | =COUNTA(IF(B2:B4>30, B2:B4)) |
Explanation: In this example, we list products and their prices. The formula =COUNTA(IF(B2:B4>30, B2:B4)) counts the number of cells in column B that are not empty and have a price over $30. The result is 1.
Example 8
Purpose of Example: To count the number of sales transactions that have been completed and have a value over $1000.
Data Tables and Formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Transaction | Status | Value | Count |
| 2 | T001 | Complete | $1500 | |
| 3 | T002 | Pending | $800 | |
| 4 | T003 | Complete | $900 | |
| 5 | COUNTA | =COUNTA(IF(B2:B4=”Complete”,IF(C2:C4>1000, C2:C4))) |
Explanation: In this example, we have a list of sales transactions, their statuses, and their values. The formula =COUNTA(IF(B2:B4="Complete",IF(C2:C4>1000, C2:C4))) counts the number of complete transactions with a value over $1000. The result is 1.
Example 9
Purpose of Example: To count the number of customers who have provided their email addresses and purchased over $50.
Data Tables and Formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Email Address | Value | Count |
| 2 | C001 | test1@test.com | $60 | |
| 3 | C002 | $40 | ||
| 4 | C003 | test3@test.com | $30 | |
| 5 | COUNTA | =COUNTA(IF(B2:B4<>””,IF(C2:C4>50, C2:C4))) |
Explanation: In this example, we have a list of customers, their email addresses, and the value of their purchases. The formula =COUNTA(IF(B2:B4<>"",IF(C2:C4>50, C2:C4))) counts the number of customers who have provided their email addresses and purchased over $50. The result is 1.
Example 10
Purpose of Example: To count the number of projects that have an assigned project manager and are completed.
Data Tables and Formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Project | Project Manager | Status | Count |
| 2 | P001 | John Doe | Complete | |
| 3 | P002 | Pending | ||
| 4 | P003 | Jane Doe | Complete | |
| 5 | COUNTA | =COUNTA(IF(B2:B4<>””,IF(C2:C4=”Complete”, C2:C4))) |
Explanation: In this example, we have a list of projects, their assigned project managers, and their statuses. The formula =COUNTA(IF(B2:B4<>"",IF(C2:C4="Complete", C2:C4))) counts the number of projects that have an assigned project manager and are completed. The result is 2.
Example 11
Purpose of Example: To count the number of employees who have submitted their timesheets and whose hours are over 40 using the SUM function.
Data Tables and Formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee ID | Timesheet | Hours | Count |
| 2 | E001 | Submitted | 45 | |
| 3 | E002 | 38 | ||
| 4 | E003 | Submitted | 42 | |
| 5 | COUNTA | =COUNTA(IF(B2:B4=”Submitted”,IF(C2:C4>SUM(30,10), C2:C4))) |
Explanation: In this example, we have a list of employees, whether they have submitted their timesheets and the number of hours they have worked. The formula =COUNTA(IF(B2:B4="Submitted",IF(C2:C4>SUM(30,10), C2:C4))) counts the number of cells where the timesheet is presented and the hours are over the sum of 30 and 10 (40). The result is 2.
Example 12
Purpose of Example: To count the number of products listed over the average price.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Product ID | Price | Count |
| 2 | P001 | $20 | |
| 3 | P002 | ||
| 4 | P003 | $35 | |
| 5 | COUNTA | =COUNTA(IF(B2:B4>AVERAGE(B2:B4), B2:B4)) |
Explanation: In this example, we list products and their prices. The formula =COUNTA(IF(B2:B4>AVERAGE(B2:B4), B2:B4)) counts the number of cells in column B that are not empty and have a price over the average price. The result is 1.
Example 13
Purpose of Example: To count the number of completed sales transactions with a value over the median value.
Data Tables and Formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Transaction | Status | Value | Count |
| 2 | T001 | Complete | $1500 | |
| 3 | T002 | Pending | $800 | |
| 4 | T003 | Complete | $900 | |
| 5 | COUNTA | =COUNTA(IF(B2:B4=”Complete”,IF(C2:C4>MEDIAN(C2:C4), C2:C4))) |
Explanation: In this example, we have a list of sales transactions, their statuses, and their values. The formula =COUNTA(IF(B2:B4="Complete",IF(C2:C4>MEDIAN(C2:C4), C2:C4))) counts the number of complete transactions with a deal over the median value. The result is 1.
Part 3: Tips and Tricks
- COUNTA can count cells with any information, including error values and empty text (“”). If you want to count only cells that contain numbers, use the COUNT function.
- If you want to count only cells that meet specific criteria, use the COUNTIF or COUNTIFS function.
- Remember that COUNTA does not count empty cells. If a cell contains a formula that returns an empty string, COUNTA will count that cell.
- You can use COUNTA with up to 255 arguments to count across multiple ranges or arrays.
- COUNTA can be combined with other functions for more complex calculations. For example, you could use COUNTA inside an IF function to calculate only if a certain number of cells contain data.