Counting is a very fundamental operation for any spreadsheet related work. COUNTA is an advanced version it’s predecessor COUNT and is widely used in Google Sheets
While COUNTA is a very straightforward and easy one to understand, there are still hidden nuances one should be aware of. In this article we’ll deep-dive into all the aspects of COUNT Function.
Provides count of number of non-blank cells
Syntax and Parameter Definition
= COUNTA(value1, [value2, value3, .....])
- value1: Can be a constant value(3,2,4 etc), cell reference(A2,B3 etc) or range(B1:B10) which will be considered for counting purpose.
Rest of the arguments have the same definition as of value1, however, they are optional to supply
COUNTA Function counts everything apart from blank/empty cells. Below is the laundry list of all that can e counted by COUNTA:
- Numbers(Integers, Decimals & % included)
- Date, Time, DateTime values
- Empty Strings like ” “
- Cells containing an apostrophe(‘)
Some important pointers to highlight:
- If the same numbers occurs twice then it will be counted twice
- Dates are inherent stored as numbers in Google Sheet, hence they’ll be counted as well
- Cells with blank values will be ignored
- If the cells which were passed onto COUNTA were updated, then COUNTA would also be updated and recalculated.
Note: To count cells containing only numerical data use COUNT Function
In the following sub-sections we have demonstrated examples to cover all the different aspects of COUNTA Function.
COUNTA Function – Basic Example
In the below example, we have selected different type of data elements and applied Counta Function over it.
COUNTA counts all the cells which meet the criteria i.e B3:B10. Hence, the output of 8 is correct.
It didn’t count B11, rightly as it was an empty cell.
COUNTA Function – Different Combination of Input types
From what was covered earlier, we know that we can supply inputs to COUNTA as constant value, cell references or range.
The output in the below example is easy to understand, however, what is worth noting is different combinations of value inputs that can be supplied to COUNTA
COUNTA Function – Case of Repeated Numbers
COUNTA Function doesn’t care about numbers getting repeated, rather it tells us count of all instances where numbers were found.
Below is the same example we used earlier. Notice we have replaced B4 from -2 to 10 making it similar to B3. The output(8) remains unchanged as expected.
COUNTA Function – Case of blank/empty cells
COUNTA Ignores any blank/empty cells coming it’s way. But they should be really empty, not just look empty.
Cells containing values like “”, ” “, ‘ may look empty, but will be counted by COUNTA.
This is very well demonstrated by the below example.
B4, B5 and B8 though might look empty, but are not. Hence, they are counted and the output is 3.
Visual Demo of COUNTA Function
Before we end here is a sample visual demonstration of COUNTA Function in action.
That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂