Array formula to sum data that meets multiple criteria.

[thegoldcoast]

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)

	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>

 

[Weazel]

maybe something like...

Unknown

	A	B	C	D	E
1		Category	2014	2015	2016
2		Bike	300	400	0
3		Mattress	2000	0	0
4		Table	0	700	900
5		Stove	0	200	0
6
7	Purchases
8	Item	Date of Purchase	Months to pay off	Monthly Payment	Total Cost.
9	Bike	8/14/2014	6	300	1800
10	Mattress	10/6/2014	8	2000	16000
11	Table	3/1/2015	12	700	8400
12	Bike	8/1/2015	6	400	2400
13	Stove	11/1/2015	5	200	1000
14	Table	3/1/2016	12	900	108

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C2 {=SUM(IF($B2=$A$9:$A$14,IF(YEAR($B$9:$B$14)=C$1,$D$9:$D$14)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[thegoldcoast]

Thank you Weazel for your reply, but for C2 it should be 1,200 because I am trying to figure out the total monthly payments I made for the year.

Your formula was helpful, I rearranged it a bit now I would like to add the multiple by D:D. How would I add that to this formula?

{=(SUM(IF($B2=$A$9:$A$14,IF(YEAR($B$9:$B$14)=C$1,(MONTH($B$9:$B$14)-12/1/2014,$D$9:$D$14))))}

Can I do something like that?

(12/1/14-8/1/14)*300=1200