Technology

Discover the details of the combined SUM and FILTER functions in Google Sheet utility.

When separating the SUM and FILTER functions, each function will have its own purpose. However, users can combine SUM and FILTER to integrate the features of both functions. If you are still unsure, let's discover how to use them together with FPT Shop!

Administrator

15 February, 2024

149

The combination of SUM and FILTER functions is a popular technique that many users apply when calculating the sum of a data set to solve various problems on Google Sheet. Let’s explore the combination of these two functions in detail through the following article on FPT Shop.

An overview of using the SUM function on Google Sheet

Get an overview of the SUM function and its usage in Google Sheet before exploring the combination of SUM and FILTER.

What is the SUM function used for?

The SUM function is used to calculate the sum of a data set on Google Sheet.

How to use the SUM function

SUM function formula: =SUM(number1, number2, …).

Explanation of function components: The number1, number2, … arguments are the numbers or range of numbers to be summed.

Here’s an example: Given the following data table including 6 information: Total revenue of a type of product, date of purchase, type of product, product name, price of 1 product, and quantity of products sold. The requirement is to calculate the total revenue of all products using the SUM function.

In this case, we use the formula: =SUM(D2:D9).

An overview of using the FILTER function on Google Sheet

Let’s get an overview of the FILTER function and its usage in Google Sheet before exploring the combination of SUM and FILTER.

What is the FILTER function used for?

The FILTER function is used when you need to filter data according to a specific condition without affecting the original data. From there, you can display the data according to your requirements.

How to use the filter function with a given condition

The formula for the FILTER function is as follows: =FILTER(Range of values; Condition 1; Condition 2, …).

Explanation of function components:

The “Range of values” is the range that contains the values you want to filter.
“Condition 1”, “Condition 2”, … are the conditions for filtering the values. There can be one or more conditions.

Here’s an example: Using the data table from the SUM function part, the requirement is to filter the products that have been sold more than 10 times. From there, identify the products that are popular.

In this case, we use the formula =FILTER(A2:A9;C2:C9>10).

Combining SUM and FILTER in Google Sheet

The formula for combining SUM and FILTER functions is:

=SUM(FILTER(Range of values; Condition 1; Condition 2, Condition 3, …), …)

Explanation of function components:

The “Range of values” is the range that contains the values you want to filter.
Each Condition 1, Condition 2, or Condition 3 is a condition for filtering the values. There can be at least one condition or none.
The result of the FILTER function will return a range of values that meet the conditions for the SUM function to calculate the sum of those values.

For example: Using the data table from the previous two parts. The requirement is to determine the revenue of products that have been sold more than or equal to 10 times.

Applying the formula: =SUM(FILTER(D2:D9;C2:C9>=10)).

Explanation of formula significance:

Use the FILTER function to filter the total revenue of products that have been sold more than or equal to 10 times. Then, the SUM function calculates the sum based on the data returned by the FILTER function. Finally, we get the desired result of 2042000.

Some examples of applying the combination of SUM and FILTER

Here are some specific examples of applying the combination of SUM and FILTER functions. These examples will help you easily imagine and flexibly apply these two functions on Google Sheet.

Filtering data based on date, month, year and calculating the sum

For example: Still using the previous data table, the requirement is to find the total revenue of products that were purchased in April.

Use the syntax: =SUM(FILTER(D2:D9;MONTH(E2:E9)=4)).

Explanation of syntax components:

The FILTER function is used to filter data within the range from cell E2 to E9. If a month of April is detected, the corresponding data in the D column will be returned.
The SUM function calculates the sum based on the data returned by the FILTER function. The result we find is 617000.

Filtering data based on string comparison and calculating the sum

For example: Using the previous data table, the requirement is to calculate the total quantity of water products with a value greater than 10000.

Using the formula: =SUM(FILTER(C2:C9;F2:F9=”N”;B2:B9>10000)).

Explanation of formula significance:

The FILTER function in this example will filter the data from cell F2 to F9. When the product type is Water, it will consider the corresponding range in the B column to see if the prices of those products are greater than 10000 or not. If yes, the corresponding data will be returned in the C column.
The SUM function calculates the sum based on the data returned by the FILTER function. The result is 47.

Filtering data based on number comparison and calculating the sum

For example: From the given data table, determine the total revenue of products with a value greater than 10000.

Use the formula =SUM(FILTER(D2:D9;B2:B9>10000)).

Explanation of the formula:

The FILTER function scans the data within the range of cells B2 to B9 to see if the product values are greater than 10000. If yes, the corresponding data will be returned in the D column.
The SUM function calculates the sum of the data filtered by the FILTER function. The result is 1807000.

Conclusion

Above is the article that shared information about the combination of SUM and FILTER functions. We hope that with the information and specific examples, you now have a better understanding of how to combine these two functions and successfully apply them when using Google Sheet!