thegoldcoast
New Member
- Joined
- Oct 31, 2017
- Messages
- 2
Hi, Everyone
I am having trouble finding the best formula to calculate how much i spent on a particular item per year. The issue that I am having is when my monthly payment spreads across multiple years, I can't seem to create a working formula that can account for the cost that spreads into the next year. I have tried using different array formulas but no luck. Would using VBA work better?
Here is an example of what I tired.
Logic:
{SUM(IF(AND[Item=Item , Date>=1/1/14 , Date<=12/1/14]), (Date - 12/1/14)*Monthly Payment,))}
Formula:
SUM ( IF( AND(B2=A9:A14, B9:B14>=1/1/14,B9:B14<=12//1/14), B9:B14-12/1/14)*D9:D14)
<tbody>
</tbody>
I am having trouble finding the best formula to calculate how much i spent on a particular item per year. The issue that I am having is when my monthly payment spreads across multiple years, I can't seem to create a working formula that can account for the cost that spreads into the next year. I have tried using different array formulas but no luck. Would using VBA work better?
Here is an example of what I tired.
Logic:
{SUM(IF(AND[Item=Item , Date>=1/1/14 , Date<=12/1/14]), (Date - 12/1/14)*Monthly Payment,))}
Formula:
SUM ( IF( AND(B2=A9:A14, B9:B14>=1/1/14,B9:B14<=12//1/14), B9:B14-12/1/14)*D9:D14)
Category | 2014 | 2015 | 2016 | |
Bike | ||||
Mattress | ||||
Table | ||||
Stove | ||||
Purchases | ||||
Item | Date of Purchase | Months to pay off | Monthly Payment | Total Cost. |
Bike | 8/14/14 | 6 | 300 | 1800 |
Mattress | 10/6/14 | 8 | 2000 | 16000 |
Table | 3/1/15 | 12 | 700 | 8400 |
Bike | 8/1/15 | 6 | 400 | 2400 |
Stove | 11/1/15 | 5 | 200 | 1000 |
Table | 3/1/16 | 12 | 900 | 10800 |
<tbody>
</tbody>