Tequilashot
New Member
- Joined
- Aug 17, 2015
- Messages
- 33
Hello, need some help consolidating a formula to allow me to have reaccuring quarterly costs based off multiple completion dates in a single row.
Row 18 is my desired result but without the need for rows 13-17.
I've attempted a SUMPRODUCT/MOD/DATEDIF in row 20 to try and arrive at the answer but no luck...anyone got any ideas?
Row 18 is my desired result but without the need for rows 13-17.
I've attempted a SUMPRODUCT/MOD/DATEDIF in row 20 to try and arrive at the answer but no luck...anyone got any ideas?
Mr Excel Question 2.xlsx | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | ||||||||||||||||||||||||
2 | Cost per unit per quarter | 500.00 | ||||||||||||||||||||||
3 | ||||||||||||||||||||||||
4 | Phase | Completion Date | Units | |||||||||||||||||||||
5 | 1 | Jan-21 | 25 | |||||||||||||||||||||
6 | 2 | Apr-21 | 30 | |||||||||||||||||||||
7 | 3 | Jul-21 | 20 | |||||||||||||||||||||
8 | 4 | Oct-21 | 43 | |||||||||||||||||||||
9 | 5 | Jan-22 | 27 | |||||||||||||||||||||
10 | ||||||||||||||||||||||||
11 | Cashflow | |||||||||||||||||||||||
12 | Sep-20 | Oct-20 | Nov-20 | Dec-20 | Jan-21 | Feb-21 | Mar-21 | Apr-21 | May-21 | Jun-21 | Jul-21 | Aug-21 | Sep-21 | Oct-21 | Nov-21 | Dec-21 | Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | |||
13 | 1 | 0 | 0 | 0 | 0 | -12,500 | 0 | 0 | 0 | -12,500 | 0 | 0 | 0 | -12,500 | 0 | 0 | 0 | -12,500 | 0 | 0 | 0 | -12,500 | ||
14 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -15,000 | 0 | 0 | 0 | -15,000 | 0 | 0 | 0 | -15,000 | 0 | 0 | 0 | -15,000 | 0 | ||
15 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -10,000 | 0 | 0 | 0 | -10,000 | 0 | 0 | 0 | -10,000 | 0 | 0 | ||
16 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -21,500 | 0 | 0 | 0 | -21,500 | 0 | 0 | 0 | ||
17 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -13,500 | 0 | 0 | 0 | -13,500 | ||
18 | Total | 0 | 0 | 0 | 0 | -12,500 | 0 | 0 | -15,000 | -12,500 | 0 | -10,000 | -15,000 | -12,500 | -21,500 | -10,000 | -15,000 | -26,000 | -21,500 | -10,000 | -15,000 | -26,000 | ||
19 | ||||||||||||||||||||||||
20 | Total | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | 0 | 0 | 0 | 0 | 0 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C6:C9 | C6 | =EDATE(C5,3) |
C12:V12 | C12 | =EDATE(B12,1) |
B13:V17 | B13 | =IFERROR((MOD(DATEDIF($C5,B$12,"m"),4)=0)*-$D5*$D$2,0) |
B18:V18 | B18 | =SUM(B13:B17) |
B20:V20 | B20 | =SUMPRODUCT(MOD(DATEDIF($C$5:$C$9,B$12,"m"),4)=0,$D$2*$D$5:$D$9) |