COUNTA Function in Excel

COUNTA Function in Excel (Table of Contents)

• What is Excel COUNTA Function?
• Examples of COUNTA Function in Excel
• How to Use the COUNTA Function in Excel?

What is Excel COUNTA Function?

COUNTA function is an important function under the group of “count” functions. It counts all the non-empty cells. It counts any type of data that includes:-

1. Numeric value
2. Texts
3. Alphanumeric values
4. Logical values
5. Error-values
6. Date/Time
7. Empty text strings(“ ”) and
8. Space in a cell

The “COUNTA”  function  syntax has the following arguments:

• Value1: An item, cell reference or a range that we want to count.
• Value2: [Optional] item, cell reference or a range that we want to count upto a maximum of 255 arguments.

CountA() function counts all the non empty cells. 

Examples of COUNTA Function in Excel

If we need to count the number of cells that are not empty within a range or several non-adjacent ranges, then COUNTA function is used. Now let us look at a few examples.

Example #1 – Using Numeric Value in COUNTA()

This function is used to count numeric values.

After applying the COUNTA function, the result is shown below.

Example #2 – Using Texts in COUNTA()

This function is used to count text values.

After applying the COUNTA function, the result is shown below.

Example #3 – Using Alphanumeric Values in COUNTA()

This function is used to count alphanumeric values.

After applying the COUNTA function, the result is shown below.

Example #4 – Using Logical Values in COUNTA()

This function is used to count logical values.

After applying the COUNTA function, the result is shown below.

Example #5 – Using Error Values in COUNTA()

This function is used to count error values.

After applying the COUNTA function, the result is shown below.

Example #6 – Using Date/Time in COUNTA()

This function is used to count date/time.

After applying the COUNTA function, the result is shown below.

Example #7 – Using Empty Text Strings in COUNTA()

This function is used to count empty text Strings.

After applying the COUNTA function, the result is shown below.

Example #8 – Using Space in a Cell in COUNTA()

This function is used to count space in a cell.

After applying the COUNTA function, the result is shown below.

Example #9 – Count the Number of Cells

The below-mentioned function counts the number of cells in column A i.e. from A1 to A5.

After applying the COUNTA function the answer is 4 because COUNTA function does not count blank cell. Here, A5 is a blank cell.

In the same example, the function is showing the result as 5 whereas in the above case it was showing as 4. The difference occurred due to the usage of the space bar in the second case in an empty cell. Both are apparently appearing as same but actually, they are not. In the above case, A5 is a blank space whereas in the second case, we have used a space bar and made the empty cell as a non-empty cell.

After applying the COUNTA function, the result is shown below.

The above functions counts the number of cells in column A i.e. from A1 to A5 and in column B, it is from B1 to B5. The answer is 8 because the COUNTA function does not count blank cells. Here, A5 and B5 are blank cells.

After applying the COUNTA function, the result is shown below.

How to Use COUNTA() Function in Excel?

Let’s understand the working of COUNTA() function with a few methods.

Method #1 – Writing the formula of CountA() function in the resulted cell

• Select the cell where the result is to be displayed.
• Write the syntax or the formula of COUNTA and then determine the range of cells that is to be counted.

• Press the Enter Key.

• Now you can press the Enter key to see the result.

Method #2 – Through Insert Function

• Select the cell where the result is to be displayed.

• Go to ‘File tab’ in the Excel Ribbon.
•  Select ‘Insert function’ under ‘Function Library’.
• ‘Insert function dialog box’ will appear and type ‘COUNTA’ in the input box corresponding to “Search for a function label”.

• Click on ‘Go’ button. Select ’CountA’ option under “Select function” label.

• Click Ok. “Function Arguments” dialog box will appear. Under ‘COUNTA’ section, input the range to be counted corresponding to ‘Value1’ label.

• Click on OK.

Method #3 – Through Auto Sum Function

• Select the cell where the result is to be displayed.
• Go to ‘File tab’ in Excel Ribbon.
• Select ‘AutoSum’ under ‘Function Library’->Select “More Functions…” through a drop-down menu.

 

• “Insert function” dialog box will appear, then type ‘COUNTA’ in the text box corresponding to “Search for a function label”

Things to Remember

• COUNTA() function is used to count cells containing values.
• It ignores “blank cells” from counting but it considers empty text string and spaces in the cell while counting the number of cells.
• Syntax of CountA() function, Count() function and Countblank() function are very similar.

Recommended Articles

This is a guide to COUNTA Function in Excel. Here we discuss How to use COUNTA Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Cell References in Excel
2. Excel Count Function
3. Count Names in Excel
4. Matching Columns in Excel
5. VBA COUNTA Function with Examples