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.
Suppose you want to count how many cells have values. To achieve this, simply use the following formula:
As a result, excel would return the result as 10.
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.
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:
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:
Explanation – Since the COUNTA function does not count the blank cells, the formula will only consider the fill cells and return their count.
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).
Thank You 🙂
- Excel COUNTIFS Function – Counting Cells with Specified Conditions
- LEN Function in Excel – Find Number of Characters
- COUNTIF Function In Excel – Counting Cells With Condition
- ROWS Function in Excel – Get Number of Rows
- Count Number of Characters in Cell or Range of Cells
- COLUMNS Function in Excel – Get Number of Columns