You can use SUMPRODUCT function in Google sheets in similar situations where you use SUMIFS or DSUM. I will explain you how to use SUMPRODUCT function in Google Sheets in simple as well as in complex calculations.
In the starting I was using this Google Sheet function for only doing basic calculations. My favourite function, when it comes to complex formulas, were either SUMIF or SUMIFS. Lately I realised that SUMPRODUCT is equal or more powerful than SUMIF or SUMIFS. Anyway I don’t go for a comparison right now. You can read it later in another tutorial. My aim here is to explain SUMPRODUCT in the possible simplest way.
Generally When to Use SUMPRODUCT function in Google Sheets?
I use SUMPRODUCT function in Google Sheets, when I want only total or grand total of product. I know an example can better convey the meaning.
The above is a simple example where you can use SUMPRODUCT function in Google Sheets. We can calculate product in either of the two ways in Column C or Column D. Then below we can get the SUM using the SUM function. SUMPRODUCT function intelligently find the product and sum it in a single cell. That’s why I told you I use SUMPRODUCT to find Grand Total or Total.
Below you can learn how to use SUMPRODUCT function in Google Sheets. I will explain the syntax first and then we can move to more complex calculations using SUMPRODUCT.
How to Use SUMPRODUCT Function in Google Sheets – Basic Use
SUMPRODUCT(array1, array2, ….)
Again I depend help of an image to explain the SUMPRODUCT syntax.
Check the syntax and take a look at the image to know the elements in the function. It reveals everything. If you can’t understand I will explain it.
In the above example we can use the SUMPRODUCT function as below.
In this A2:A4 is array 1 and B2:B4 is array two. The above function is equal to (3*4)+(2*5)+(1*6)
In the above example, the three formulas in Cell C5, D5 or C8 give the same result. If you understand the basic of how to use of SUMPRODUCT function in Google Sheets now we can switch to the complex form.
Complex Use of SUMPRODUCT Function in Google Sheets
You can use multiple criterion inside the SUMPRODUCT Formula to get a result like DSUM. I’m not going to do a comparison. Still I’m using the two formulas in the below example to get same result. If you don’t know how to use DSUM, you can ignore the DSUM part in the example.
One again I’m requesting you to learn the basic use of SUMPRODUCT before going to the below complex use.
We just want to calculate the sales volume of “Philip” from area “East” and “West”. How can we do that using SUMPRODUCT function?
The below formula will do that magic
The formula has two parts as highlighted. The first part will check in Column A for “Philip”, and check for Column B for “East” and give the output of corresponding value. There is no multiplication in this case as there is only one column with numeric value.
In the second part of the formula SUMPRODUCT function will check for “Philip” in Column A, then look for Column B for “West” and bring the corresponding value. Then it adds the two values to get the final result.
From this complex SUMPRODUCT formula you can understand one thing. You can use certain arrays in the formula (see the above syntax) as criterion.
The shortened version of the same formula above is as below.
The above two formulas are the examples to how to use multiple criterion in same field in SUMPRODUCT.
Example to the use of DSUM instead of SUMPRODUCT
You can skip this if you are yet to learn DSUM. You can learn DSUM from our site. Search with the keyword DSUM in the search filed on the top navigation panel.
I just post one example below, which shows how DSUM and SUMPRODUCT deals same data differently.
The main difference here is ‘normally’ DSUM requires criterion as cell reference that also with field label. But you can use criterion inside the DSUM formula too. But I avoid it as I like the shorter and ‘cuter’ DSUM formula.
The above DSUM simple formula produce the result which you get from the complex SUMPRODUCT function we detailed above. But SUMPRODUCT is more flexible.