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...

Formulas & Functions

Show and Tell

John Jairo Vergara Domínguez

Brass Contributor

May 22, 2018

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!

If_Flight_Example (modified).xlsx12 KB