How to Use COUNTA Function in Excel (3 Suitable Examples)

In Microsoft Excel, the COUNTA function is generally used to count the cells containing the non-empty character(s) only. In this article, you’ll get to learn how you use this COUNTA function efficiently in Excel.

The above screenshot is an overview of the article, representing an application of the COUNTA function in Excel. You’ll learn more about the dataset as well as the methods to use the COUNTA function properly in the following sections of this article.

Introduction to the COUNTA Function

• Function Objective:

Counts the number of cells in a range that is not empty.

• Syntax:

=COUNTA(value1, [value2],…)

• Arguments Explanation:

• Return Parameter:

Total counts of the cells in a numeric value.

COUNTA Function in Excel: 3 Simple Examples

1. Using COUNTA Function for a Single Range of Cells in Excel

Since we know now, that the COUNTA function counts only non-empty cells, we’ll have a look at how this function works for the following chart containing several random data. There are text values, number strings, error values, logical values, blank cells, wildcards, and spaces in the chart. And all of them are lying in a single column or we can call it a single range of cells as no other column is going to be considered here right now.

📌 Steps:

➤ Select the output cell B16 and type:

➤ Press Enter and you’ll find the total count of the non-blank cells as 8.

But there are a total of 9 cells under the List 1 header in the data chart, and if you notice, B8 and B12 are blank cells. So, the resultant count should have been 7 instead of 8, right? The fact is, cell B12 seems to be blank, but there is a space character, and that’s why the cell will not be counted as empty. COUNTA function only excludes a cell where no single character is found.

2. Using COUNTA for Multiple Range of Cells in Excel

Now, the following table has two columns of random data. The blank and space-contained cells have been highlighted now for the convenience of understanding the characters in the cells. As we’re now dealing with two different columns, we can input these two different ranges of cells in the COUNTA function. Or, we can even select the entire range of cells containing two adjacent columns and input it to the first argument only.

To count non-empty cells from the following table containing List 1 and List 2, now have a glance at the simple steps.

📌 Steps:

➤ Select cell B16 and type any of the two from the following formulae:

Or,

➤ Press Enter and you’ll find the total count as 16. The function has excluded the blank cells B8 and C9 here while counting.

3. Use of COUNTA Function with Mixed Data Inputs in the Arguments

Not only the cells, but we can also manually input different types of values or data in the arguments of the COUNTA function, and all of them will be counted as non-empty strings or values. In the following dataset, we’ve used the table mentioned in the last section but now we’ll include some other random data or values manually in the COUNTA function and see how it works.

📌 Steps:

➤ In the output cell B16, we can type:

➤ Press Enter, and the function will return 20 since we’ve added 4 more random data inside the COUNTA function.

Difference between COUNT and COUNTA Functions in Excel

The basic difference between the COUNT and COUNTA functions is- COUNT function counts only numbers and excludes all other values as well as empty strings whereas the COUNTA function counts only non-empty cells. The picture below is an example of how these two functions work differently in the output cells B16 and B19.

Read More: Difference Between COUNT and COUNTA Functions in Excel

An Alternative to COUNTA Function to Count Cells That Are Not Empty

There is a suitable alternative to the COUNTA, and that is the COUNTIF function. COUNTIF function will let you define criteria for the range of cells so you’ll have the room for instructing the function to exclude empty cells while counting.

📌 Steps:

➤ In the output cell B16, the related formula with COUNTIF function to exclude all empty cells should be:

➤ After pressing Enter, you’ll get the return value as 8.

💡 Things to Keep in Mind

🔺 You’re allowed to input up to 255 arguments in the COUNTA function.

🔺 If the count of the cells seems confusing or wrong, then please check if any of your empty cells contain a space character as the COUNTA function includes a cell containing space characters while counting.

🔺 While inputting text data as the argument, make sure you’re using double quotes (“ “) outside the text string.

🔺 If you need to count a range of cells containing anything or not, then you have to use COUNTA() + COUNTBLANK() functions together.

🔺 If you need to count cells containing numeric values only, then go for the COUNT function only.

🔺 To count the blank cells only, use the COUNTBLANK function.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all of the methods mentioned above to use the COUNTA function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Excel COUNTA Function: Knowledge Hub

• How to Use COUNTA Function with Criteria in Excel
• How to Use COUNTA from SUBTOTAL Function in Excel
• Dynamic Ranges with OFFSET and COUNTA Functions in Excel
• Excel COUNTA Function Not Working

<< Go Back to Excel Functions | Learn Excel