Forum Discussion
Tricky 3-Part Formula
Tricky for me! Aircraft flight time invoice. I need to calculate monthly totals based on the following: ~First 5 hours (0.1 - 5.0) at $2000/hr. ~Next 5 hours (5.1 - 10) at $2500/hr ~Over 10 hour...
Hi to both!
I Leave two more options (in Matt's file). One with helper range, an another without it.
Helper Range --> J1:K4 ( {"Hours","Value";0,2000;5,2500;10,3000} )
Formula with helper range:
[G2]: =SUMPRODUCT((B2>J$2:J$4)*(B2-J$2:J$4)*(K$2:K$4-N(+K$1:K$3)))
Formula without helper:
[H2]: =SUMPRODUCT((B2>{0;5;10})*(B2-{0;5;10})*{2000;500;500})
The advantage of these methods is that they allow you to handle multiple cumulative ranges without the need to lengthen the formula too much for each condition. Check file. Blessings!
Nice formulas. Great utilization of SumProduct formulas. Especially like the second one.
Thx for your kind comment. Blessings!
