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