## What Is COUNTA Excel Function?

The

COUNTA Excel Functionin Excelcalculates all non-blank cells containing numerical and alphabetical values in a table array. It is a Statistical Excel Function and returns only a numerical value.

For example, in the following image, we will count all the non-blank cells having numbers and alphabets in column A using the COUNTA Excel function in cell B2.

*=COUNTA(A2:A5) *

The output comes as ‘3’.

##### Table of contents

###### Key Takeaways

- The
**COUNTA formula**enables users to count the cells containing numeric values, text values, cell references, ranges, date, time, and error values in Excel. It is a Statistical Excel Function. - The syntax of the COUNTA function is =COUNTA(value1,[value2],…). Here, value1 is a mandatory argument, and value2 is an optional argument. They are the values to be counted.
- The
**COUNTA function**does not count on an empty table array and returns the output as 0. - The
**COUNTA function**has a hidden feature, i.e., the empty string within quotes (“ “) is passed as an argument is also counted.

### COUNTA() Excel Syntax

The image below is an explanation of the arguments accepted by the **COUNTA function in Excel:**

**value1**= The first cell reference or the range we want to count. It is the mandatory argument.**value2**= The second cell reference in excel or the range we want to count. It is the optional argument.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

### How To Use COUNTA Function?

We can use the **COUNTA function in Excel** in two ways:

#### 1. Using Excel Menu

- Choose the empty cell which will contain the result.
- Go to the
**“Formulas”**tab and click it. - Select
**“More Functions”**from the drop-down menu. - Click on
**“Statistical”**from the drop-down menu. - Select
**“COUNTA”**from the drop-down menu. - A window called
**“Function Arguments”** - As the number of arguments, enter the value in the
**“Value 1”**and**“Value 2”**. - Select
**OK**.

#### 2. Manually On The Sheet

- Select the empty cell
- Type =COUNTA(
- Double click on the COUNTA command
- Select the range of the cell or array
- Press “Enter.”

The image below depicts values in various formats, including numbers, decimal numbers, numbers in percentage, letters, fruit names, prices with the ‘$’ sign, and no text string within (“ “) quotes. Here, we will try to calculate the number of all non-blank cells using the **COUNTA function**.

In the table, the data is reflected as below:

- Column A shows values in various formats

The steps to count the number of non-blank cells are as follows:

**First, we will choose the cell where we want the result to show up. Cell B2 would be the cell in this case.****Enter the formula to count values from the table. Select the value1, which is the range from the starting cell address to the ending cell address of the table, i.e., A2:A10. The complete COUNTA Excel function will be:***=COUNTA(A2:A10)***After entering each value in the preceding step, press the “Enter” key. The result is shown in cell B2 as ‘7’ in the image below.**

Therefore, the**COUNTA function**counts all the numbers, decimal numbers, numbers in percentage, letters, fruit name, price with the ‘$’ sign, and no text string within (“ “) quotes but does not count the blank spaces in the table. For example, the total cells in the table are 9, but there are two empty cells, which the**COUNTA function**did not count.

### Examples

Let us look at some advanced COUNTA Excel function examples to understand how it works:

#### Example #1 – Continuous Rectangular Range

The image below depicts the data of people working in a firm from different locations. The values include their ID, Date of joining, Location, Age, and Gender. Here, we will try to calculate the number of all cells having numbers and letters using the **COUNTA function**.

In the table, the data is reflected as below:

- Column A shows ID
- Column B contains the Date
- Column C contains the Location
- Column D contains the Age
- Column E contains Gender

The steps to count the number of non-blank cells are as follows:

__Step 1__**:** First, we will choose the cell where we want the result to show up. Cell E7 would be the cell in this case.

** Step 2:** Enter the formula to count cells with values from the table. Select the value1, which is the range from the starting cell address to the ending cell address of the table, i.e., A2:E6. The complete COUNTA Excel function will be:

*=COUNTA(A2:E6)*

__Step 3__**:** After entering each value in the preceding step, press the “Enter” key. The results are shown in cell E7 as ‘22’ in the image below.

Therefore, the **COUNTA function** counts all the numbers, words, and dates but does not count the blank spaces in the table. For example, the total cells in the table are 25, but there are three empty cells in the table, which the **COUNTA function** did not count.

#### Example #2

The image below depicts the data of fruit vendors. The values include the fruits and their prices. Here, we will try to calculate the number of all cells having numbers and letters using the **COUNTA function**.

In the table, the data is reflected as below:

- Column A shows Fruits
- Column B contains the Price

The steps to count the number of non-blank cells are as follows:

__Step 1__**:** First, we will choose the cell where we want the result to show up. Cell C2 would be the cell in this case.

** Step 2:** Enter the formula to count cells with values from the table. Select the value1, which is the range from the starting cell address to the ending cell address of the table, i.e., A2:B10. The complete COUNTA Excel function will be:

*=COUNTA(A2:B10)*

__Step 3__**:** After entering each value in the preceding step, press the “Enter” key. The result is shown in cell C2 as ‘13’ in the image below.

Therefore, the **COUNTA function** counts all the numbers and words but does not count the blank spaces in the table. For example, the total cells in the table are 18, but there are five empty cells, which the **COUNTA function** did not count.

#### Example #3 – Return 0

The **COUNTA function **typically counts non-blank cells in an array**.** However, the image below depicts no value. Therefore, we will try to calculate the empty cells having no value using the **COUNTA function**.

In the table, the data is reflected as below:

- Column A shows a blank cell

The steps to count the number of non-blank cells are as follows:

__Step 1__**:** First, we will choose the cell where we want the result to show up. Cell B2 would be the cell in this case.

** Step 2:** Enter the formula that needs to count values from the table. Select the value1, the cell from the table, i.e., “A2”. The complete COUNTA Excel function will be:

*=COUNTA(A2)*

__Step 3__**:** After entering the value in the preceding step, press the “Enter” key. The result in cell B2 is ‘0’ in the image below.

Therefore, the **COUNTA function** counts all the cells with values but does not count the blank spaces in the table. As a result, the table has no value, so the **COUNTA function** returns 0 as the output.

**Important Things To Note**

- The
**COUNTA function**does not count blank cells. - It simply counts the existing values rather than adding them up.
- If the arguments are invalid, it returns an error.
- The
**COUNTA function**is part of the COUNT excel function group. **COUNTA function**counts text values and numeric values.- The function can handle up to 255 characters.

**Frequently Asked Questions** (FAQs)

**How to do COUNTA in Excel?**

Below is the step by step process of counting non-blank cells using the COUNTA function:

1. Select the empty cell

2. Type =COUNTA(

3. Double click on the COUNTA command

4. Select the range of the cell or array

5. Press “Enter.”

For example, the image below shows the Annual Budget of the firm’s Departments. The values include the Departments and the amount of the Annual Budget. Here, we will try to calculate the number of all cells having numbers and letters and no text String within (“ “) quotes using the **COUNTA function**.

In the table, the data is reflected as below:

– Column A shows the Departments

– Column B contains the Annual Budget

– Cell C2 calculates the number of numeric value

Enter the COUNTA Excel function in Cell C2.*=COUNTA(A2:B6)*

Press the “Enter” key. The results are shown in cell C2 as ‘8’ of the image below.

Therefore, the **COUNTA function** counts the number and letters and no text string within (“ “) quotes as a value.

**Where is the COUNTA function in Excel?**

One can activate the **COUNTA function in Excel **using the following steps:

1. Choose the empty cell which will contain the result.

2. Go to the **“Formulas” **tab and click it.

3. Select **“More Functions” **from the drop-down menu.

4. Select **“Statistical” **from the drop-down menu.

5. Select **“COUNTA”** from the drop-down menu.

6. A window called **“Function Arguments” **appears.

7. As the number of arguments, enter the value in the **“Value 1”** and **“Value 2”**.

8. Select **OK.**

**How to COUNTA function in Excel with multiple criteria?**

The image below depicts the mixed content. The values include the letters, phrases, numbers, dates, and empty text strings within (“ “) quotes. Here, we will try to calculate the number of all cells having content except blank cells with multiple criteria, i.e., passing multiple arguments using the **COUNTA function**.

In the table, the data is reflected as below:

– Columns A and B contain Contents** **

The steps to count the number of non-blank cells are as follows:** Step 1:** First, we will choose the cell where we want the result to show up. Cell B7 would be the cell in this case.

**Enter the formula that needs to count values from the table. Select the value1, which is the range from the starting cell address to the ending cell address of the table, i.e., A2:A6.**

__Step 2__:**Select the value2, which ranges from the starting cell address to the ending cell address of the table, i.e., B2:B6. The complete COUNTA Excel function will be:**

__Step 3__:

*=COUNTA(A2:A10,B2:B6)***After entering the values in the preceding step, press the “Enter” key. The result is shown in cell B7 as ‘7’ in the image below.**

__Step 4__:Therefore, the

**COUNTA function**counts all the numbers, letters, phrases, dates, and empty text strings within (“ “) quotes but does not count the blank spaces in the table. The total cell in the table is 10, but there are three empty cells in the table, which the

**COUNTA function**did not count.

**Download Template**

This article must be helpful to understand the **COUNTA function in Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to COUNTA Excel Function. Here we learn using the COUNTA formula with basic and advanced examples and a downloadable excel template. You can learn more from the following articles –

## Leave a Reply