Posted by Just hint for me. Today I’ve learned useful shortcut how to make quickly conditional SumProduct in Excel in one step (without creating new column).Until now I need to follow at least two steps.
(a) creating new column with conditional product summary (multiplication of two column values) using IF function,
(b) using SUM function for summing values in the new columnn
The solution requires additional column which could not be very friendly for desired sheet’s layout and it created unneccessary overhead.
Now things goes more easier. It can be done in one step without creation of new column. Lasts to coerce SumProduct function using conditional multiplication, for instance in the following (hidden) syntax:
=SUMPRODUCT(–(A2:A9=1);B2:B9;C2:C9)
The trick consists from inserting — (two dashes in sequence) to formula which coerce function using condition (A2:A9=1) from example above to filter out rows ‘where row value in column A is not equal to 1‘ from multiplication.
Yes, it possible to combine conditions, in that case simply repeat trick with inserting — again to define another condition as a second parameter (then you will use four parameters in SUMPRODUCT formula).
Finally, the trick is working with another functions, for instance could be very handy for normal SUM.
Great tool! I’d never have worked that out.