Conditional SumProduct in Excel by only one step

Flattr this!

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.

One Response to Conditional SumProduct in Excel by only one step

  1. Lee says:

    Great tool! I’d never have worked that out.

Leave a Reply

Your email address will not be published. Required fields are marked *

Powered by WordPress | Designed by: suv | Thanks to trucks, infiniti suv and toyota suv