Usually, Excel is famous for storing and organizing data, however, sometimes you may have to count cells in a spreadsheet containing specific values. You may have to count cells containing a list of products you purchased in the current month.
When it comes to counting cells, Excel has COUNT and COUNTA functions. Both of these functions are highly useful, however here we will discuss the COUNTA function in Excel.
Using the COUNTA function, you can count cells with multiple kinds of data values. These values may include,
- Text
- Boolean Values
- Date
- Time
- Error-values
- Empty text strings (“”)
You can find the COUNTA function in the Excel Statistical functions group. It helps in counting the total number of cells that have values of any type. Apart from this, the COUNTA function is commonly referred to as the Excel COUNTIF Not Blank formula.
Syntax of the COUNTA Function
Here is the syntax used for this function:
=COUNTA(value1, [value2], …)
Let’s have a look at the arguments in detail:
Value1: It contains values that you need to count.
Value2: It contains values that are needed to be counted.
The main difference between these two arguments is that the first one is mandatory, whereas the second argument is optional. These arguments could have a range, an array of values, a cell, a value, or a reference to cell ranges.
Also, note that you can use around 2555 arguments in MS Excel 2007 version whereas the old versions can deal with just 30 arguments.
How to Use COUNTA Function in Excel to Count Non-Blank Cells?
When you have non-blank ranges, whether they are single or multiple, the COUNTA function is super helpful in counting the number of cells. Your cell range could be non-adjacent. For instance, in the range B1:B50, you can count cells using this formula:
=COUNTA(B1:B50)
This function provides the total number of value arguments given. Here the value argument is considered as a parameter that you cannot consider a cell or a range of cells. You can use this function to count the following categories of data:
- The number of clients in a list
- The number of deliveries in a hospital
- The number of employees with senior posts
- The number of tests passed by students
- The number of transactions in the current month
How to Use COUNTA Function in Excel in Single Range?
Below you will find the dataset we are using in this example. Here we need to find out the number of non-blank cells provided in a range A2:A7.
For this we can use the formula:
“=COUNTA(A2:A7)”
For A2 to A7, the COUNTA function will count the number of cells containing data. You will get 5 as cell A5 does not have any value. Here you can see the A5 cell is empty that’s why it is not counted whereas all the other cells are counted.
How to Use COUNTA Function in Excel in Several Criteria (COUNTIFS)?
The COUNTIFS function can handle multiple ranges and multiple criteria. Below is the syntax used for it:
COUNTIFS(criteria_range1, criteria, [criteria_range2, criteria2]…)
Let’s put this formula in an example:
Suppose you need to find out how many “Apples” in column A reached $200 and more sales in column B. for this the following formula is used:
=COUNTIFS(A2:A15, “apples”, B2:B15,”>=200”)
The cell references can also be used as criteria because it makes the COUNTIFS more useful.
Things to Consider about the COUNTA Function
The COUNTA function is only helpful when you need to count some logical values. On the other hand, if the cells contain only numbers, you have to use the COUNT function.
Basically, the COUNT function has multiple series, such as COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.
The COUNTIF and COUNTIFS both functions are helpful only when you need to count the numbers that match specific criteria.
Final Thoughts
So, you can now use the COUNTA function easily when you need to count the number of cells containing any kind of value. The above information can be used effectively because it helps in clearing up your mind when you start dealing with your dataset.
