SUMPRODUCT is an incredibly versatile function that can be used to sum and count like SUMIFS or COUNTIFS, but with more flexibility.
Drill down
Consider the below table, I have the sale amounts for cars, buses and trains from January to April. If I want to sum the sales of 2 or more months for a particular vehicle then we can use the SUMPRODUCT formula.
We can’t use SUMIFS only works when because the sum range exists in 1 column
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Type | Jan Sales | Feb Sales | Mar Sales | Apr Sales |
| 2 | Car | 48 | 39 | 96 | 82 |
| 3 | Bus | 70 | 52 | 37 | 72 |
| 4 | Train | 0 | 88 | 35 | 47 |
Sum using SUMPRODUCT
Formula to get the Jan – Feb sales for a car.
=SUMPRODUCT((A2:A4="Car")*(B2:C4))
Formula for the Jan and March to April sales for a car, you just add the 2 SUMPRODUCT formulas together as you can’t use non-contiguous ranges in the formula.
=SUMPRODUCT((A2:A4="Car")*(B2:B4))+SUMPRODUCT((A2:A4="Car")*(D2:E4))
Counting using SUMPRODUCT
If you want to check how many months there were sales (i.e sale amount more than 0) then use this…
=SUMPRODUCT((A2:A4="Car")*(B2:E4