What Is COUNTA Function In Google Sheets?
The COUNT All, or the COUNTA Google Sheets function, helps users count the number of cell values in the selected dataset regardless of the values in it, whether textual or numeric.
While using the COUNTA function In Google Sheets, ensure not to select the dataset header names, as that will be considered as a value too. For example, we have a data of products and their units sold on different dates. We will count the number of products sold using the COUNTA in Google Sheets.
For example, we have a data of products and their units sold on different dates. We will count the number of products sold using the COUNTA in Google Sheets.
Select cell D2, enter the formula =COUNTA(A2:A9) and press “Enter”, as shown.
The output is shown above, i.e. 8 products were bought on the dates mentioned.
Key Takeaways
- The COUNTA Google Sheets function is a Statistical function which returns the count of all the selected values in a single or multiple datasets, irrespective of the values in it. Except that there must be values present and not blank or empty cells.
- We must provide a minimum of one value for the formula to execute. It the selected cell range is empty, then the formula returns 0.
- It considers cells containing numeric values, alpha-numeric values, symbols, texts, logical values, error values, zero, or space characters and counts them.
- The COUNTA function is from the group of COUNT functions that also has COUNTIF, COUNTBLANK, COUNTIFS, COUNTUNIQUE, COUNTUNIQUEIFS, DCOUNT, DCOUNTA that returns the count of all the required values that satisfy the specific criteria. Therefore, ensure to enter the right function name, to get the right formula results.
- We can use the COUNTA functions with other function, such as UNIQUE(), etc.
- As an alternate option, we can use the Conditional Formatting method to highlight the required cells, and count the colored cells as well.
Syntax
The syntax of the COUNTA Formula in Google Sheets is,
Thearguments of the COUNTA Formula in Google Sheets are,
- value1: It is the cell range or the dataset selected to count the number of cells with any kind or format of values. It is a mandatory argument.
- [value2, …]: It is an additional cell range to count along with the value1. It is an optional argument.
How To Use COUNTA Function In Google Sheets?
We can use the COUNTA Google Sheets function in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
#Method 1: Access from the Google Sheets ribbon
- Step 1: Choose an empty cell for the output -> select the “Insert” tab -> click the “Function” option right arrow -> click the “Statistical” option right arrow -> select the “COUNTA” function, as shown below.
Step 2: The “COUNTA” formula appears, as shown below. Enter the argument as cell reference.
#Method 2: Enter the formula in the worksheet manually ->
Step 1: Select an empty cell for the output.
Step 2: Type =COUNTA( in the cell, as shown below. [Alternatively, type =C or =COUNT and double-click the COUNTA from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some of the COUNTA Google Sheets function examples and count the values that fulfils the set criteria.
Example #1
Consider the table given below that consists of 15 students’ assignment submission data. We will count the number of students who have submitted their assignments using the COUNTA Google Sheets function on the submitted date.
The procedure to count students who submitted the assignments is,
Select cell E2, enter the formula =COUNTA(D2:D16) and press “Enter”, as shown below.
The function looks for the cell values in the range D2:D16, that indicates that the assignments are submitted. As the student submits and the submission date is updated against the student’s name, automatically the COUNTA formula cell also gets updated.
Example #2 – Combining COUNTA with UNIQUE function
We have the cosmetics brands campaign data given below with their region and sales. We will be – Combining COUNTA with UNIQUE function to discard duplicate values and return the count of different brands.
The procedure to use the UNIQUE and COUNTA Google Sheets function is,
Select cell A10, enter the formula =COUNTA(UNIQUE(B2:B7) and press “Enter”, as shown below.
The output is shown above as 4, i.e., there are 4 brands and 3 are repeated. So, the UNIQUE function had ignored the repeated or duplicate values, and the COUNTA function had returned the count of the unique values.
Example #3 – Using COUNTA with ARRAYFORMULA
Consider the dataset that consists of employee details, such as their names, ID’s, department, their salaries from January to June. Let us find the total count using COUNTA with ARRAYFORMULA.
The procedure to use the COUNTA and ARRAYFORMULA functions is,
Select cell C13, enter the formula =ARRAYFORMULA(COUNTA(E2:J11)) and press “Enter”, as shown below.
We notice that the total count with or without the ArrayFormula is the same when the entire cell range is selected. Therefore, we can conclude that COUNTA Google Sheets function does not work with ArrayFormula, but works with functions, such as COUNT, COUNTIF, COUNTIFS, etc.
Example #4 – Using Multiple Ranges in COUNTA
We have multiple datasets given below that consists of smartphone models, the units ordered on different dates. Let us find the total count using Multiple Ranges in COUNTA.
The procedure to perform COUNTA on multiple cell rangesis,
Select cell B11 and enter the formula =COUNTA(A2:A9,E2:E9), as shown below.
Press “Enter” to get the output shown below, indicating that 16 smartphone models are ordered on 2 different dates.
Important Things To Note
- In the COUNTA Google Sheets function, if the result cells are placed below the list, ensure not to add information to the list, as it will push the cells down and return error as the cell references change. To avoid such situations, it’s better to add it at the right of the dataset, unless we do not add extra columns.
- The result cells should not overlap the list. Ensure to leave a row or a column in-between the dataset and the result cell, if we are creating the table below or beside the dataset, respectively.
- If the header is selected, the function generates the count of the overall dataset.
- We get the “#NA” error, if no argument is provided.
- Ensure that the blank cells do not have any leading spaces, or it will count them as well.
- Ensure to add the direct cell values within double-quotes if it is text and numeric values without double-quotes.
Frequently Asked Questions (FAQs)
The COUNTA Google Sheets function isn’t working because of the following reasons:
1) The argument values or cell reference are not organized properly.
2) If the cell range selected is modified or deleted, such as new data added, the blank cells are filled, or few data are removed, then the formula gets altered w.r.t references.
3) It does not insert the cell value we give directly for the arguments within double-quotes.
We often forget in which category a function falls, here, the “COUNTA” function. Then, we can insert the function as follows:
Choose an empty cell -> select the “Insert” tab -> click the “Function” option right arrow -> click the “All” option right arrow à select the “COUNTA” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
We can use the same path to insert other count functions, such as, COUNTBLANK, COUNT, COUNTIF, COUNTIFS, COUNTUNIQUE, COUNTUNIQUEIFS, as they are just above and below the COUNTA function, as seen in the above image.
Alternatively, we can find the Functions icon to insert the COUNTA Google Sheets function by following the path shown below.
• Choose an empty cell -> click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option à click the “All” option right arrow à select the “COUNTA” function, as shown below.
• The COUNT function counts only the numeric values within a given range, but the COUNTA function counts all cell values both textual and numbers.
• Both have 2 arguments, one mandatory and the other is optional. However, COUNT is a Math function and COUNTA is a Statistical function.
We can insert the Google Sheets UNIQUE function as follows:
Choose an empty cell for the output -> select the “Insert” tab -> click the “Function” option right arrow -> click the “Filter” option right arrow -> select the “UNIQUE” function, as shown below.
Download Template
This article must help understand COUNTA function in Google Sheets with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is COUNTA function in Google Sheets. We learn its syntax & how to use it to count all types of cell values with examples. You can learn more from the following articles. –
Leave a Reply