Google Sheets FILTER Function | SUMPRODUCT | SUM | Extract Data | Condition | Spreadsheet Tutorial

Use the Google Sheets FILTER, SUMPRODUCT, and SUM functions to calculate, for example, the total inventory and cost for a specific product. Let's work on a spreadsheet project that uses the above three functions for the said task.

Say there are two stores, Store A and Store B, owned by the same company selling fruits and other groceries.

Project Objective

Use the Google Sheets FILTER, SUMPRODUCT, and SUM functions to calculate the total inventory and cost of product apple in Store A and Store B.

Here is the spreadsheet data for Store A:

Product Code Cost/Kg. Qty.
Apple 10312 210 90
Grapes 10542 81 78
Banana 10322 60 150
Apple 10312 230 109
Papaya 10566 92 106

Here is the spreadsheet data for Store B:

Product Code Cost/Kg. Qty.
Apple 10312 209 83
Grapes 10542 83 74
Banana 10322 64.5 168
Apple 10312 207 72
Papaya 10566 93.8 90

Filter a Specific Product based on its Product Code

Assume that each product is given an unique product code and the same for apple is 10312.

Say the spreadsheet has four columns each, for Store A and B, with labels,
Product, Code, Cost/kg. and Qty. respectively, adjacent to each other.

Let's use the FILTER function to extract the data for product apple, using its
code 10312.

Here is the format of the Google Sheets FILTER function formula:

=FILTER(range, condition1, [condition2], […])

Say that the range is A6:I10, which covers the data of the two stores, excluding the labels.

condition1 is A6:A10=10312.

Note that the range A6 to A10 has the product codes.

We are not using the optional condition2 and one or more of subsequent conditions.

Let's construct the FILTER function formula, specifying the values for range
and condition1:

=FILTER(A6:I10, A6:A10=10312)

Say that the formula returns these values for Store A and B:

Apple 10312 210 90 Apple 10312 132 83
Apple 10312 230 109 Apple 10312 207 72

Each data set column is product, code, cost per kg., and quantity respectively.

Calculate Total Inventory of Product 10312 Using the SUM Function Formula

Here is the format of the SUM Function Formula:

=SUM(value1, [value2], […])

value1 is the first range of numbers, a cell reference, or a hard-coded value
to be added with value2.

value2 is the second range of numbers, a cell reference, or a hard-coded value to be added with value1.

Say that quantity column data, for Store A, is in the spreadsheet range D6:D10 and the same for Store B is in the range I6:I10.

We are ready to construct the SUM function formula, specifying the values for value1 and value2:

=SUM(D6:D10, I6:I10)

This function formula will return 595 (99+109+83+72).

Calculate Total Inventory Cost of each store Using the SUMPRODUCT Function Formula

The format of the Google Sheets SUMPRODUCT function formula is:

=SUMPRODUCT(array1, [array2], […])

array1 is the first range of numbers, with each number in the range is multiplied with the corresponding number in array2. The product is then added to the second product and so on.

array2 is the second range of numbers, with each number in the range is multiplied with the corresponding number in array1. The product is then added to the second product and so on.

Assume that array1 range address, with the label Cost/Kg., of Store A is C6:C10.

Assume that array2 range address, with the label Qty., of Store A is D6:D10.

=SUMPRODUCT(C6:C10, D6:D10)

The formula will return 69040.

Let's use the SUMPRODUCT function formula to calculate total inventory cost of Store B:

=SUMPRODUCT(H6:H10, I6:I10)

The formula will return 57761.

Finally, as the last part of this spreadsheet project, let's calculate the
grand total of inventory cost using SUM function formula:

Assume that the SUMPRODUCT function output for Store A and Store B is stored in the spreadsheet cells Q10 and L10 respectively.

=SUM(Q10, L10)

The formula will return 126801.

Take a look at this Google Sheets tutorial, which gives the steps to use the FILTER, SUMPRODUCT, and SUM functions to calculate the total inventory and cost of specific product.