Trying to add R5:R7 to this formula. Essentially it will use the value of R to dictate which "Owing" column to put it in.
For example, Bob (P5) was on the 12 month payment plan so I would need that to populate in U14 whereas Mary (Q6) and Tom (Q7) are on the 6 and 9 mo payment plan so I would need those to populate in columns V14 and W14.
I tried adding the R value to the end of the formula but it keeps giving me an error. =SUMPRODUCT((MONTH(P14)<=T$5:T$7+1)*(MONTH(P14)>=MONTH(Q$5:Q$7))*(YEAR(P14)<=YEAR(W$5:W$7))*U$5:U$7)*(R5:R7="12")
How do I add the value of R so that it knowns which column to put it in?
For example, Bob (P5) was on the 12 month payment plan so I would need that to populate in U14 whereas Mary (Q6) and Tom (Q7) are on the 6 and 9 mo payment plan so I would need those to populate in columns V14 and W14.
I tried adding the R value to the end of the formula but it keeps giving me an error. =SUMPRODUCT((MONTH(P14)<=T$5:T$7+1)*(MONTH(P14)>=MONTH(Q$5:Q$7))*(YEAR(P14)<=YEAR(W$5:W$7))*U$5:U$7)*(R5:R7="12")
How do I add the value of R so that it knowns which column to put it in?
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| U5:U7 | U5 | =S5/T5 |
| W5:W7 | W5 | =EDATE(Q5,T5) |
| T14 | T14 | =A38/M6+(A35/3) |
| T15 | T15 | =A38/M6+(A35/3) |
| T16 | T16 | =A38/M6 |
| S14 | S14 | =B38/M7+(B35/6)+(B23/6)+(B26/6) |
| S15 | S15 | =B38/M7+(B35/6)+(B26/6) |
| S16 | S16 | =B38/M7+(B35/6) |
| S17 | S17 | =B38/M7+(B35/6) |
| S18 | S18 | =B38/M7+(B35/6) |
| S19 | S19 | =B38/M7 |
| R14 | R14 | =C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9) |
| R15 | R15 | =C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9) |
| R16 | R16 | =C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9) |
| R17 | R17 | =C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9) |
| R18 | R18 | =C38/M8+(C35/9)+(C26/9)+(C29/9) |
| R19 | R19 | =C38/M8+(C35/9)+(C29/9) |
| R20 | R20 | =C38/M8+(C35/9) |
| R21 | R21 | =C38/M8+(C35/9) |
| Z14:Z24,AA14:AA25,Y14:Y25 | Z14 | =R14-V14 |
| Q14 | Q14 | =(D5/J5)/M9*(J5)+(D8/J8)/M9*(J8)+(D11/J11)/M9*(J11)+(D14/J14)/M9*(J14)+(D17/J17)/M9*(J17)+(D20/J20)/M9*(J20)+(D23/J23)/M9*(J23)+(D26/J26)/M9*(J26)+(D29/J29)/M9*(J29)+(D32/J32)/M9*(J32)+(D35/J35)/M9*(J35)+(D38/J38)/M9*(J38) |
| Q15 | Q15 | =(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D8/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12) |
| Q16 | Q16 | =(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12) |
| Q17 | Q17 | =(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D14/12)+(D17/12)+(D20/12) |
| Q18 | Q18 | =(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D17/12)+(D20/12) |
| Q19 | Q19 | =(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D20/12) |
| Q20 | Q20 | =(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12) |
| Q21 | Q21 | =(D38/M9)+(D35/12)+(D26/12)+(D29/12)+(D32/12) |
| Q22 | Q22 | =(D38/M9)+(D35/12)+(D29/12)+(D32/12) |
| Q23 | Q23 | =(D38/M9)+(D35/12)+(D32/12) |
| Q24 | Q24 | =(D38/M9)+(D35/12) |
| Q25 | Q25 | =(D38/M9) |
| Q26 | Q26 | =(D41/M9) |
| T26 | T26 | =Q26-S26 |
| U14 | U14 | =SUMPRODUCT((MONTH(P14)<=T$5:T$7+1)*(MONTH(P14)>=MONTH(Q$5:Q$7))*(YEAR(P14)<=YEAR(W$5:W$7))*U$5:U$7)*(R5:R7="12") |
| U15:U36 | U15 | =SUMPRODUCT((MONTH(P15)<=T$5:T$7+1)*(MONTH(P15)>=MONTH(Q$5:Q$7))*(YEAR(P15)<=YEAR(W$5:W$7))*U$5:U$7) |
