Excel COUNTA Function – Count Cells Containing Values

In one of our previous blogs, we learned how to use the COUNT function in excel to count the number of cells that contain numbers. In this blog, we would learn another counting excel function called the COUNTA Function in Excel.

The term COUNTA represents ‘Count All’. As the name suggests, this function counts everything.

Unlike the COUNT formula, the COUNTA function returns the count of all the cells containing something or some values. It means that this function and formula count all non-blank cells within a specified cell range.

Let us now deep dive into the COUNTA formula, and learn how it works in excel. Here we go 😎

When To Use COUNTA Function in Excel

The COUNTA Function is a statistical excel formula that is used to count all the filled cells within the specified cell range. This means that the excel COUNTA formula ignores and does not count the blank cells.

Syntax and Arguments

=COUNTA(value1, [value2], [value3] ……….)

The below point explains the arguments of the excel COUNTA formula.

  • value1 – In this argument, specify the range of cells within which you want to count non-blank cells.

The value argument accepts a maximum of 255 such values.

Examples Of Using COUNTA Function in Excel

In this section of the tutorial, we would learn how to use the COUNTA function to get the count of all the non-blank cells in a range of cells.

Ex. 1 – COUNTA Formula – Basic Example

The below image shows a list of values in a cell in excel.

Cells containing Mix of Values

Suppose you want to count how many cells have values. To achieve this, simply use the following formula:

=COUNTA(A1:A10)

As a result, excel would return the result as 10.

Using COUNTA function to Count Non-Blank Cell

This means that within the selected cell range (A1:A10), there are 10 cells that are filled with something.

Note, that the COUNTA function does not count the blank cells. Suppose, you also include column B while selecting the input argument range, the excel will still result in 10 (i.e. count of filled cells). See the below formula and image as an example.

=COUNTA(A1:B10)
COUNTA Function ignores blank cells
Infographic - COUNTA Formula Function in Excel

Ex. 2 – Using Optional Arguments – Count Non-Consecutive Filled Cells

Many a time, you may have the values in non-consecutive cells range, like shown below:

Non Consecutive Cells - COUNTA Function

In the above example, the data is placed in three different non-consecutive cell ranges – A1:A4, C2:C4, and C7:C9.

In such a scenario, you may use any of the below formulas, to count the number of filled cells:

=COUNTA(A1:C9)

Explanation – Since the COUNTA function does not count the blank cells, the formula will only consider the fill cells and return their count.

COUNTA Function to Count Non-Consecutive Cells

OR

=COUNTA(A1:A4,C2:C4,C7:C9)

Explanation – In the above formula, we have used the optional value arguments as well (i.e. value2 and value3) to specify different cell ranges A1:A4, C2:C4, and C7:C9. The result in anyways remains the same (i.e. 10).

Using Optional Arguments of COUNTA Function Excel

Thank You 🙂

Leave a Comment