COUNTA Function in Google Sheets

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.

Purpose

Provides count of number of non-blank cells

Syntax and Parameter Definition

= COUNTA(value1, [value2, value3, .....])
  1. 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

Expected Output

COUNTA Function counts everything apart from blank/empty cells. Below is the laundry list of all that can e counted by COUNTA:

  1. Numbers(Integers, Decimals & % included)
  2. Date, Time, DateTime values
  3. Text
  4. Boolean(TRUE/FALSE)
  5. Errors
  6. Formulas
  7. Empty Strings like ” “
  8. Cells containing an apostrophe(‘)

Some important pointers to highlight:

  1. If the same numbers occurs twice then it will be counted twice
  2. Dates are inherent stored as numbers in Google Sheet, hence they’ll be counted as well
  3. Cells with blank values will be ignored
  4. 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

Examples

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 Function - Example - Google Sheets

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.

COUNTA - Different Input Styles

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 Repeated Values - Google Sheets

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.

COUNTA Blank Cells - Google Sheets

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.

COUNTA Demo - Google Sheets

That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Liked the content?? Then share the word!!!

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply