SUMPRODUCT is an incredibly versatile function that can be used to sum and count like SUMIFS or COUNTIFS, but with more flexibility.
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
|1||Type||Jan Sales||Feb Sales||Mar Sales||Apr Sales|
Sum using SUMPRODUCT
Formula to get the Jan - Feb sales for a car.
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.
Counting using SUMPRODUCT
If you want to check how many months there were sales (i.e sale amount more than 0) then use this…