How To Use COUNTA Function in Google Sheets

The COUNTA function in Google Sheets is useful when you want to return the number of values in a dataset.

This function can take multiple arguments, including ranges. All values are counted, regardless of whether they are numeric or text.

The rules for using the COUNTA function in Google Sheets are as follows:

  • The function requires at least one argument (either a value or range) to be counted.
  • The function then outputs the total number of values from the values and ranges specified.

Let’s take a look at a quick example of this function in action.

Let’s say we have a dataset of values with missing values. We would like to find out how many non-empty entries there are for each column. We can think of scenarios like finding whether the percentage of a dataset has missing values, or counting how many slots in a schedule has been taken already. The COUNTA function makes it easy for us to perform this count.

Now that we have a grasp of when to use the COUNTA function, let’s look into how to use the function and later work on an actual sample spreadsheet.

 

 

The Anatomy of the COUNTA Function

The syntax of the COUNTA function is as follows:

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

Let’s dissect this formula and try to understand what each of these terms means:

  • = the equal sign is how we start all functions in Google Sheets.
  • COUNTA() is our COUNTA function. It returns the number of values in a dataset.
  • value1 refers to the first value or range to consider when counting.
  • value2 and subsequent arguments are additional values or ranges to count using the COUNTA function.
  • COUNTA counts all values, including zero-length strings and whitespace. Values that appear more than once are counted separately.

 

 

A Real Example of Using COUNTA Function

Let’s look into a real example of the COUNTA function being used in a Google Sheets spreadsheet.

In the sheet below, we have two columns, Values and Colors. We used several count functions to describe this dataset. The difference between COUNT and COUNTA is made apparent when comparing how they count the values seen in column A. The COUNTA function counts all non-empty cells, even if they are non-numeric. The COUNT function only counts numeric data.

COUNTA Function in Google Sheets used to count values per column

 

To get the count in cell E2, we just need to use the following formula:

=COUNTA(A2:A22)

You can make your own copy of the spreadsheet above using the link I have attached below. 

In the example below, we have a spreadsheet of dinner table seating arrangements for an upcoming gathering. Notice how some tables have empty slots for future guests. We can use the COUNTA function to get the total number of non-empty slots.
Using COUNTA Function in Google Sheets to count taken seats in a guest list

 

If you’re ready to try out the COUNTA function in Google Sheets, let’s begin writing it ourselves!

 

 

How to Use COUNTA Function in Google Sheets

In this section, we will go through each step needed to start using the COUNTA function in Google Sheets. This guide will show you how to use the function to count the number of the taken slots there are in a guest seating list, as seen in the previous example.

Follow these five steps to start using the COUNTA function:

  1. In this example, we’ll start by selecting cell F2. This cell is where we will place our count later.
    Selecting a cell to place the COUNTA function

  2. Next, we just simply type the equal sign ‘=‘ into our formula bar, followed by ‘COUNTA(‘. 
  3. Details on how to use the COUNTA function may appear on a tooltip pop-up. We can minimize the window by clicking on the arrow on the top-right-hand corner.
    typing COUNTA function into formula bar

  4. Next, we need to select the range A2:C9 so we can count all the taken slots. We can simply hit Enter on the keyboard to let the function return the number of non-empty cells in that range.
    COUNTA function returns 15 total seats taken
    In this case, we now know that there are 15 taken seats.
  5. To find the number of seats still available, we can subtract our COUNTA result from the total number of seats. Alternatively, you may also use the COUNTBLANK function to count the number of blank cells in a range.
    subtracting the seats taken from the total number of seats

 

 

Frequently Asked Questions (FAQ)

  1. Why does my COUNTA formula count blank cells?
    It’s possible that cells may appear blank but are actually not empty cells. For example, an empty string (“”) or a single apostrophe will not return any text. These examples are still considered non-empty cells by the COUNTA formula.
  2. What happens if you reference a cell without any data?
    The COUNTA function counts each cell provided individually. Cells or values that appear more than once are not counted as one.
    Multiple overlapping ranges


    In the example above, we have overlapping ranges. Since we don’t count unique values, many cells are counted more than once, leading to a count of 31 rather than 15.

This article shows how easy it is to use the COUNTA function to count how many non-empty cells there are in a given range.

You can now use the COUNTA functions in Google Sheets along with the various other Google Sheets formulas available to create powerful spreadsheets that solve your problems.

Stay notified of new guides about Google Sheets by subscribing to our newsletter!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like