Calulating yearly interest payments

[dazed+confused]

Hi,

I've been ripping my hair out for the past few hours and finally gave up. I'm hoping someone on this board can put me out of my misery I want to calculate the total interest paid each year. I have one table with the interest payments per month:


Date Bank Interest
2012: December 584 €
2013: January 583 €
2013: February 582 €
2013: March 581 €
2013: April 579 €
2013: May 578 €
2013: June 577 €
2013: July 576 €
2013: August 575 €
2013: September 574 €
2013: October 572 €
2013: November 571 €
2013: December 570 €
2014: January 569 €
2014: February 568 €
2014: March 567 €
2014: April 565 €
2014: May 564 €
2014: June 563 €
2014: July 562 €
2014: August 561 €
2014: September 559 €
2014: October 558 €
2014: November 557 €
2014: December 556 €
2015: January 554 €
2015: February 553 €
2015: March 552 €
2015: April 551 €
2015: May 549 €

And I am trying to calculate the values in this table:

2012 2013 2014 2015
Total Interest:

Can anyone help?

Thanks,
Dazed+Confused

 

[Dave Patton]

Excel Workbook
	A	B	C
1	Date Bank Interest	Year	Amount
2	2012: December 584	2012	584
3	2013: January 583	2013	583
4	2013: February 582	2013	582
5	2013: March 581	2013	581
6	2013: April 579	2013	579
7	2013: May 578	2013	578
8	2013: June 577	2013	577
9	2013: July 576	2013	576
10	2013: August 575	2013	575
11	2013: September 574	2013	574
12	2013: October 572	2013	572
13	2013: November 571	2013	571
14	2013: December 570	2013	570
15
16		2012	584
17		2013	6,918
18
4a

Excel 2003

Cell Formulas
Range	Formula
B2	=LEFT(A2,4)+0
C2	=MID(A2,LEN(A2)-4,3)+0
C16	=SUMIF($B$2:$B$14,B16,$C$2:$C$14)
C17	=SUMIF($B$2:$B$14,B17,$C$2:$C$14)

With

- year extracted or shown in Column B

- amount extracted or shown in Column C

- year criteria in B16

N.B. extract of your data
Copy formulas down and edit ranges as necessary

 

[Dave Patton]

Edit
- assumption Your data is in Column A

=SUMPRODUCT(--(LEFT($A$2:$A$14,4)+0=B16),(MID($A$2:$A$14,LEN($A$2:$A$14)-4,3)+0))

Edit ranges as necessary; I condensed the sample for the above.

 

[dazed+confused]

Thanks Dave! Actually, my data is as follows:

Column A: YYYY: month
Column B: EUR

I thought there might be something I could do using SUMIF, DATE and YEAR functions but I couldn't get that to work. I will try the LEFT($A$2:$A$14,4) function to see if that works better (and SUMPRODUCT as I don't know this formula).

I should have probably specified this in the original question (as obviously I am also having troubles posting a table on this forum

Cheers,
still dazed, but slightly less confused.

 

[Dave Patton]

Criteria such as 2012 in D2 etc
Try either of the following

=SUMIF($A$2:$A$100,D2&"*",$B$2)

=SUMPRODUCT(--((LEFT($A$2:$A$14,4)+0)=D2),($B$2:$B$14))


Copy formulas down and edit ranges as necessary in the formula so that all your data is considered.