Search code examples
excelexcel-formulasumifs

EXCEL Formula: Multiply SUMIFS outcome with the corresponding row value from a different column

I am trying to use SUMIFS and multiply its output with the a values from the corresponding row. Example:

Example

In This example I will use just 1 criteria for the SUMIFS, but the concept remains the same: SUMIFS(C2:C5; B2:B5; "=Sum"). This would return 5 + 6 = 11 Now I want to multiple that by the corresponding probability. Meaning 5 * 50% + 6 * 20%: SUMIFS(C2:C5; B2:B5; "=Sum") *

Any idea how to include the probability in the equation?

Thanks!

Solution

  • You can't use SUMIFS for this unless you add a helper column - try using SUMPRODUCT instead

    =SUMPRODUCT((B2:B5="Sum")+0;C2:C5;D2:D5)

    SUMPRODUCT multiplies all the arrays/ranges and then sums the result, so we can include your value and probability ranges with a conditional array based on "Sum" in the include range

    With a helper column you can just use column E to multiply C and D, e.g. this formula in E2 copied down

    =C2*D2

    and then use SUMIFS like this

    =SUMIFS(E2:E5;B2:B5;"Sum")