How am I able to make excel range from min to max year

[Jacko1996]

So i have sets of data ranging from a certain year. What I want to do is run a yearly column from min to the max, with the sum of the rainfall next to that.

It looks like below

1893	0	0	0	0	0	0	0	0	0	0	0	2
1894	439.6	106.8	537.1	0	0	0	0	0	0	0	0	0
1895	0	0	0	0	0	0	0	0	0	0	0	191.7
1896	234.9	725.2	89.6	29.5	3
1907	145.8	306.3	337.9	34.6	115.3	176.7	27.5	28.7	15.3	35.1	76.8	120.7

<colgroup><col span="8"><col><col><col span="3"></colgroup><tbody>
</tbody>

However I want it to look like this

1893	SUM OF RAINFALL FOR THAT YEAR
1894	SUM OF RAINFALL FOR THAT YEAR
1895	SUM OF RAINFALL FOR THAT YEAR
1896	SUM OF RAINFALL FOR THAT YEAR
1907	SUM OF RAINFALL FOR THAT YEAR

<tbody>
</tbody>

Thanks

 

[svendiamond]

Just insert a new column after the year for "total" and enter something like:

=sum(c2:c100) and fill down

 

[Jacko1996]

No i know how to do that, how do i make the year row start at min year of the data and end at the max year?

 

[AhoyNC]

Like this?
Copy formula down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Year	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
2	1893	0	0	0	0	0	0	0	0	0	0	0	2
3	1894	439.6	106.8	537.1	0	0	0	0	0	0	0	0	0
4	1895	0	0	0	0	0	0	0	0	0	0	0	191.7
5	1896	234.9	725.2	89.6	29.5	3
6	1907	145.8	306.3	337.9	34.6	115.3	176.7	27.5	28.7	15.3	35.1	76.8	120.7
7
8	Year	Total
9	1893	2
10	1894	1083.5
11	1895	191.7
12	1896	1082.2
13	1907	1420.7
14
Sheet

 

[Jacko1996]

Yeah that would work however the range must be dynamic to update to different data sets placed in so the min and max years will change.

 

[AhoyNC]

Maybe something like this.
Copy the formulas down as far as you think year range for years needs to be.

NOTE: the formula in A11 for years is an array formula and must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Year	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
2	1893	0	0	0	0	0	0	0	0	0	0	0	2
3	1894	439.6	106.8	537.1	0	0	0	0	0	0	0	0	0
4	1895	0	0	0	0	0	0	0	0	0	0	0	191.7
5	1896	234.9	725.2	89.6	29.5	3
6	1907	145.8	306.3	337.9	34.6	115.3	176.7	27.5	28.7	15.3	35.1	76.8	120.7
7
8	Min=	1894
9	Max=	1896
10	Year	Total
11	1894	1083.5
12	1895	191.7
13	1896	1082.2
14
15
16
CTRL-SHIFT-ENTER.