2

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!

user9011819
  • 21
  • 1
  • 4

1 Answers1

1

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")

barry houdini
  • 45,615
  • 8
  • 63
  • 81