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
COUNTAfunction. 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
COUNTAcounts 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
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.
To get the count in cell E2, we just need to use the following formula:
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.
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
- In this example, we’ll start by selecting cell F2. This cell is where we will place our count later.
- Next, we just simply type the equal sign ‘=‘ into our formula bar, followed by ‘COUNTA(‘.
- Details on how to use the
COUNTAfunction may appear on a tooltip pop-up. We can minimize the window by clicking on the arrow on the top-right-hand corner.
- 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.
In this case, we now know that there are 15 taken seats.
- To find the number of seats still available, we can subtract our
COUNTAresult from the total number of seats. Alternatively, you may also use the
COUNTBLANKfunction to count the number of blank cells in a range.
Frequently Asked Questions (FAQ)
- 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
- What happens if you reference a cell without any data?
COUNTAfunction counts each cell provided individually. Cells or values that appear more than once are not counted as one.
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!