One way to do this is to use the IF function directly inside of SUMPRODUCT.
Another more common alternative is to use Boolean logic to apply criteria.
Both approaches are explained below.
Basic SUMPRODUCT
The SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
The classic SUMPRODUCT problem multiplies two ranges together and sums the product directly without ahelper column.
For example, in the worksheet above, we have Quantity and Price, but no line item total.
This formula works nicely.
However, it’s not obvious how to calculate aconditional sumwith SUMPRODUCT.
For example, how can you calculate the value of all records where the color is “Red”?
One option is to use theIF functiondirectly, as explained in the next section.
Note thatarray2holds Quantity andarray3holds Price.
We do this because we want anumeric result, for reasons that become clear below.
The Boolean values that make uparray1act like a filter when the arrays are multiplied together.
The final result returned by SUMPRODUCT is $750.
Additional conditions can be added with additional IF statements.
The traditional solution to this problem is to switch toBoolean logic, as explained below.
As before,array1acts like a filter when the arrays are multiplied together.
As with the IF function, the same pattern can be repeated to add more conditions.
Another advantage is that the math operation can be changed to apply a different pop in of logic.
More than one condition can be tested by nesting IF functions.
