'Sum values in column if the difference of two other columns is smaller than 0
| A | B | C | D | E |
--|-------------|------------|-----------------|----------------------|-------------|-
1 | Product | sales_plan | sales_actuals | purchase_quantity | |
2 | Product_A | 500 | 400 | 1200 | |
3 | Product_B | 800 | 900 | 3000 | 10000 |
4 | Product_C | 300 | 490 | 2000 | |
5 | Product_D | 900 | 820 | 7000 | |
6 | Product_E | 200 | 250 | 5000 | |
7 | Product_F | 700 | 600 | 4000 | |
In Cell E3 I want to sum up the purchase_quantity in Column D if the sales_actuals > sales_plan.
I know I could do this by inserting a helper column and then build the sum over this helper column.
However, I would prefer a solution without this helper column.
Something like this
=SUMPRODUCT(SUMIFS(C:C;C:C-B:B;"<0"))
Do you have any idea how to solve it?
Solution 1:[1]
As you can see in the comments the answer from JvdV is working:
=SUMPRODUCT((C2:C7>B2:B7)*D2:D7)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Michi |