Summarize Based on Year

[kumara_faith]

Hi,

I have the following table:

Book1
	C	D	E	F	G	H	I	J	K
2	Table 1						Table 2
3	Price	Year	Share	Dividend			Year	Share	Dividend
4	2/5/2015	2015	1	25			2015	16	185
5	3/5/2015	2015	12	100			2016	6	80
6	8/7/2015	2015	3	60			2017	4	45
7	1/5/2016	2016	1	25
8	8/7/2016	2016	5	55
9	9/2/2017	2017	4	45
Sheet2

In Table 1, I have my input sheet. In Table 2, I am trying to summarize the total shares and dividend for each year. The correct results is in Table 2. Is there a way to use a formula to achieve this ? Appreciate all the help.

 

[etaf]

as the year is just shown as text a sumif() should do
=SUMIF($B$3:$B$8,$G3,C$3:C$8)

Book1
	A	B	C	D	E	F	G	H	I
1	Table 1						Table 2
2	Price	Year	Share	Dividend			Year	Share	Dividend
3	42126	2015	1	25			2015	16	185
4	42127	2015	12	100			2016	6	80
5	42193	2015	3	60			2017	4	45
6	42491	2016	1	25
7	42559	2016	5	55
8	42775	2017	4	45
9
Sheet1

Cell Formulas
Range	Formula
H3:I5	H3	=SUMIF($B$3:$B$8,$G3,C$3:C$8)

 

[kvsrinivasamurthy]

For Year, in G3 copy down

=IFERROR(INDEX($B$3:$B$100,AGGREGATE(15,6,ROW($B$3:$B$100)/(($B$3:$B$100<>"")*ISERROR(MATCH($B$3:$B$100,$G$2:$G2,0))),1)-ROW($A$2)),"")

For Share and Dividend use etaf's formula.

 

[kumara_faith]

Hi etaf and kvsrinivasamurthy,

Thank you for the solution. It worked and appreciate your patience and valuable time. Have a great day ahead.?

 

[etaf]

you are welcome