The SUMPRODUCT formula in Google Sheets multiplies corresponding items within the arrays of equal size and returns the sum of the results. We come across many such situations where we need to multiply items across arrays and add them up. For instance, please consider the following example.

SUMPRODUCT Formula - Illustration 1

We have items, their prices, quantities and sale prices. We calculated the corresponding sale prices in column D, i.e. cells D2 through to D6. To do so, we used multiplication formulas. And, we used the SUM formula to calculate the total sales price across all the items (cell D7). Certainly, this is a rather lengthy process. Fortunately, we have a much simpler alternative.

Syntax

SUMPRODUCT(array1, [array2, …])

  • array1 – array or address reference to the range of cells whose values, the SUMPRODUCT formula will multiply with corresponding values in the second array or range of cells.
  • array2, … – optional and additional arrays or address references to the range of cells with the same size as array1.The SUMPRODUCT formula will multiply these values with corresponding values in the first array or range of cells.

Usage: SUMPRODUCT Formula

We have seen a use case above in which we used the SUM formula to achieve the desired output. Let us re-use the same data set to try the SUMPRODUCT formula.

SUMPRODUCT Formula - Illustration 2

In the snapshot above, we keyed in the SUMPRODUCT formula in cell D8. Please compare the value it returned with that in the cell D7. It is exactly the same. The range B2:B6 is the input for array1, while C2:C6 is the input for array2.

While this is already much handier than the former approach, this gets even better when the source data set is altered, especially when we insert new rows. Let’s try inserting a new record between 4th and 5th rows.

SUMPRODUCT Formula - Illustration 3

We will notice the SUMPRODUCT formula is now relocated to cell C9. It automatically adjusted the calculation for the latest modification, while the SUM formula did not change. If we were to make the SUM formula work, we need to update the formula in D5 accordingly.

Interestingly, we can also use this formula similar to that of the COUNTIFS formula. We demonstrate an example below. The formula is counting (or rather summing) the instances where all the array conditions evaluate to TRUE.

SUMPRODUCT Formula - Illustration 4

For the purposes of better understanding the above case, please note that the product of TRUE, TRUE and TRUE will be 1. Whereas, the product of any combination that involves a FALSE evaluates to 0, as shown in the snapshot below.

SUMPRODUCT Formula - Illustration 5

 

 

Comments are closed.