Suggest some formula

[sanjay_godambe]

Test.xls
	A	B	C	D	E
1	Credit Rating	Issuer	Coupon Rate (%)	Maturity Date	Face Value (Rs.)
2
3
4	Sovereign	GOI	10.18%	11-Sep-26	200,000.00
5	Sovereign	GOI	10.03%	09-Aug-19	125,000.00
6	Sovereign	GOI	7.46%	28-Aug-17	50,000.00
7	Sovereign	GOI	12.25%	02-Jul-10	900,000.00
8	Sovereign	GOI	6.85%	05-Apr-12	300,000.00
9	Sovereign	GOI	7.46%	10-Oct-17	470,000.00
10	Sovereign	GOI	7.38%	03-Sep-15	610,000.00
11
12
13					2,655,000.00
14
15
16
17	Answer Should Be
18	Years	Face value
19	2010	900,000.00
20	2011	0
21	2012	300,000.00
22	2013	-
23	2014	-
24	2015	610,000.00
25	2016	-
26	2017	520,000.00
27	2018
28	2019	125,000.00
29	2026	200,000.00
30	Total	2,655,000.00
Sheet1

 

[SteveO59L]

Use a vlookup() using the year as the lookup value

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(YEAR($D$4:$D$10)=A19),$E$4:$E$10)

in B19 and drag down.

 

[sanjay_godambe]

Hi Fair,

Thanks a lot it works perfectly.

Regards

Sanjay

 

[sanjay_godambe]

Hi Fair,

Thanks for your replay their us one problem it is working fine in my test sheet but on my master sheet the error is coming like #Value!

Can u suggest why it is ? As i tried lot


Regards
sanjay

 

[fairwinds]

Refering to the formula I posted, my guess is that something that is not a true date value can be found on the $D$4:$D$10 range.