Regular Functions in Google Sheets (sum, average, counta and countunique)

Google Sheets is a powerful and versatile spreadsheet application that lets you perform various calculations, analyses, and manipulations with your data. One of the features that makes Google Sheets so useful is the availability of functions. Functions are predefined formulas that take one or more arguments and return a result. You can use functions to simplify complex formulas, automate repetitive tasks, or create custom solutions for your specific needs.

In this article, we will introduce some of the most common and useful functions in Google Sheets: sum, average, counta, and countunique. We will explain what they do, how to use them, and provide some examples to illustrate their applications.

Sum Function

The sum function is one of the most basic and frequently used functions in Google Sheets. It calculates the total of a set of numbers, values, or cells. The syntax for the function is:

SUM(value1, [value2, ...])

value1 is the first value or range to include in the sum. This argument is required.
value2, ... are additional values or ranges to include in the sum. These arguments are optional. You can have up to 30 arguments in total.

The sum function can accept both numbers and cell references as arguments. For example:

=SUM(10, 20, 30) returns 60, the sum of three numbers.
=SUM(A1:A5) returns the sum of the values in cells A1 through A5.
=SUM(10, A1:A5) returns the sum of 10 and the values in cells A1 through A5.

You can also use the sum function to add numbers across different sheets or workbooks. For example:

=SUM(Sheet1!A1:A5) returns the sum of the values in cells A1 through A5 on Sheet1.
=SUM('https://docs.google.com/spreadsheets/d/1aBcD2eF3gH4iJ5k6l7m8n9oP0qR8sT9u/edit#gid=0'!A1:A5) returns the sum of the values in cells A1 through A5 on another Google Sheet with the given URL.

The sum function can also handle non-numeric values in its arguments. For example:

If a cell contains text, an error, or is empty, it will be ignored by the sum function.
If a cell contains a logical value (TRUE or FALSE), it will be converted to 1 or 0 respectively by the sum function.
If a cell contains a date or time value, it will be converted to a serial number by the sum function.

Average Function

The average function is another common and useful function in Google Sheets. It calculates the arithmetic mean of a set of numbers, values, or cells. The syntax for the function is:

AVERAGE(value1, [value2, ...])

value1 is the first value or range to include in the average. This argument is required.
value2, ... are additional values or ranges to include in the average. These arguments are optional. You can have up to 30 arguments in total.

The average function can accept both numbers and cell references as arguments. For example:

=AVERAGE(10, 20, 30) returns 20, the average of three numbers.
=AVERAGE(A1:A5) returns the average of the values in cells A1 through A5.
=AVERAGE(10, A1:A5) returns the average of 10 and the values in cells A1 through A5.

You can also use the average function to calculate the mean of numbers across different sheets or workbooks. For example:

=AVERAGE(Sheet1!A1:A5) returns the average of the values in cells A1 through A5 on Sheet1.
=AVERAGE('https://docs.google.com/spreadsheets/d/1aBcD2eF3gH4iJ5k6l7m8n9oP0qR8sT9u/edit#gid=0'!A1:A5) returns the average of the values in cells A1 through A5 on another Google Sheet with the given URL.

The average function can also handle non-numeric values in its arguments. For example:

If a cell contains text, an error, or is empty, it will be ignored by the average function.
If a cell contains a logical value (TRUE or FALSE), it will be converted to 1 or 0 respectively by the average function.
If a cell contains a date or time value, it will be converted to a serial number by the average function.

Counta Function

The counta function is a handy function in Google Sheets that counts the number of cells that are not empty in a range or a list of values. The syntax for the function is:

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

value1 is the first value or range to count. This argument is required.
value2, ... are additional values or ranges to count. These arguments are optional. You can have up to 30 arguments in total.

The counta function can accept any type of value or cell reference as arguments. For example:

=COUNTA(10, 20, 30) returns 3, the number of non-empty values.
=COUNTA(A1:A5) returns the number of non-empty cells in the range A1 through A5.
=COUNTA(10, A1:A5) returns the number of non-empty values and cells in 10 and the range A1 through A5.

You can also use the counta function to count the number of non-empty cells across different sheets or workbooks. For example:

=COUNTA(Sheet1!A1:A5) returns the number of non-empty cells in the range A1 through A5 on Sheet1.
=COUNTA('https://docs.google.com/spreadsheets/d/1aBcD2eF3gH4iJ5k6l7m8n9oP0qR8sT9u/edit#gid=0'!A1:A5) returns the number of non-empty cells in the range A1 through A5 on another Google Sheet with the given URL.

The counta function is useful when you want to count the number of entries, responses, or items in a list or a table. For example, you can use it to count how many people answered a survey question, how many products are sold in a month, or how many tasks are completed in a project.

Countunique Function

The countunique function is a special function in Google Sheets that counts the number of unique values in a range or a list of values. The syntax for the function is:

COUNTUNIQUE(value1, [value2, ...])

value1 is the first value or range to count unique values from. This argument is required.
value2, ... are additional values or ranges to count unique values from. These arguments are optional. You can have up to 30 arguments in total.

The countunique function can accept any type of value or cell reference as arguments. For example:

=COUNTUNIQUE(10, 20, 30) returns 3, the number of unique values.
=COUNTUNIQUE(A1:A5) returns the number of unique values in the range A1 through A5.
=COUNTUNIQUE(10, A1:A5) returns the number of unique values in 10 and the range A1 through A5.

You can also use the countunique function to count the number of unique values across different sheets or workbooks. For example:

=COUNTUNIQUE(Sheet1!A1:A5) returns the number of unique values in the range A1 through A5 on Sheet1.
=COUNTUNIQUE('https://docs.google.com/spreadsheets/d/1aBcD2eF3gH4iJ5k6l7m8n9oP0qR8sT9u/edit#gid=0'!A1:A5) returns the number of unique values in the range A1 through A5 on another Google Sheet with the given URL.

The countunique function is useful when you want to count the number of distinct elements, categories, or groups in a data set. For example, you can use it to count how many different countries are represented in a survey, how many different products are sold in a month, or how many different tasks are assigned in a project.

Conclusion

In this article, we have introduced some of the most common and useful functions in Google Sheets: sum, average, counta, and countunique. We have explained what they do, how to use them, and provided some examples to illustrate their applications. These functions can help you perform various calculations, analyses, and manipulations with your data in Google Sheets.

References:

Google Sheets function list - Google Docs Editors Help

9 Basic Google Sheets Functions You Should Know - How-To Geek

COUNTUNIQUE - Google Docs Editors Help

Regular Functions in Google Sheets (sum, average, counta and countunique)