How to Use COUNTA Function in Excel (3 Methods)

Dataset Overview

The screenshot below provides an overview of this tutorial, demonstrating the application of the COUNTA function in Excel. We’ll use this dataset to illustrate the methods.

Introduction to the COUNTA Function

• Function Objective:

The COUNTA function counts the number of cells in a range that are not empty.

• Syntax:

=COUNTA(value1, [value2],…)

• Arguments Explanation:

• Return Parameter:

Total count of non-empty cells as a numeric value.

Method 1 – Using COUNTA Function for a Single Range

Since the COUNTA function only considers non-empty cells, let’s explore how it works using the following chart with various data types (text values, number strings, error values, logical values, blank cells, wildcards, and spaces). All data lies in a single column (or range of cells).

• Select the output cell B16 and enter:

• Press Enter to find the total count of non-blank cells (result: 8).

Note: Although there are 9 cells under the “List 1” header, cell B12 appears blank due to a space character, which prevents it from being counted as empty. COUNTA excludes cells where no single character is found.

Method 2 – Using COUNTA for Multiple Ranges

Consider the table with two columns of random data (highlighted for clarity).

To count non-empty cells from List 1 and List 2:

• Select cell B16 and enter either of the following formulas:

Or,

• Press Enter to get the total count (result: 16). The function excludes blank cells B8 and C9.

Method 3 – Mixed Data Inputs in COUNTA Arguments

You can manually input different values or data types in COUNTA arguments, and they’ll be counted as non-empty.

• In the output cell B16, enter:

• Press Enter to get a count of 20 (including the additional random data).

Difference between COUNT and COUNTA Functions

• The COUNT function counts only numbers and excludes other values and empty strings.
• The COUNTA function counts only non-empty cells.
• See the example below (output cells B16 and B19).

Read More: Difference Between COUNT and COUNTA Functions in Excel

Alternative to COUNTA: COUNTIF Function

• The COUNTIF function allows you to define criteria for cell ranges, excluding empty cells during counting.
• In output cell B16, use the formula:

• Press Enter to get a return value of 8.

Things to Keep in Mind

1. Number of Arguments:

• • You can input up to 255 arguments in the COUNTA function.

2. Space Characters:

• • If the cell count seems confusing or incorrect, check whether any empty cells contain space characters.
  • The COUNTA function includes cells with space characters while counting.

3. Text Data and Double Quotes:

• • When inputting text data as an argument, use double quotes (” “) around the text string.

4. Combining COUNTA and COUNTBLANK:

• • To count a range of cells containing anything (non-empty) or nothing (blank), use both COUNTA() and COUNTBLANK() functions together.

5. Counting Numeric Values Only:

• • If you need to count cells containing numeric values only, use the COUNT function.

6. Counting Blank Cells Only:

• • For counting blank cells exclusively, use the COUNTBLANK function.

Download Practice Workbook

You can download the practice workbook from here:

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