Hi Shital. I am assuming you didn't get any responses to the last time you posted this, as you
probably wouldn't then be posting again. If that is the case then perhaps you could take a look
at your explanation and see if you can expand on it, or give some more detail. You talk about
2003, yet it does not figure in your example data.
Are you perhaps looking to be able to input a year and then have the sum total of the areas for
that year appear. If so then how do you handle say 1998 in 1997-1998 and 1998-1999. I'm assuming
you don't want it in both, else you would be double counting, so I'm assuming the second is the
one you want. Is the data in your first column actually in the format 1997-1998 as a text
string - If so then you may want to split the column into two, using Data / Text To Columns. You
can work with it as you have it, but it's an unnecessary complication.
Once you have done that you can then just use a simple SUMIF formula to add up all the areas, eg:-
A B C D
1 YearS YearF Pur Area
2 1996 1997 5 1300
3 1996 1997 10 350
4 1996 1997 15 290
5 1997 1998 10 1200
6 1997 1998 5 1000
7 1997 1998 20 1300
8 1998 1999 10 2000
9 1998 1999 20 700
With the year 1998 in say cell H1, the following formula will sum all areas for that year:-
=SUMIF(B2:B9,H1,D2
9)
If you didn't want to alter your data, then it would look as follows:-
A B C
1 Year Pur Area
2 1996-1997 5 1300
3 1996-1997 10 350
4 1996-1997 15 290
5 1997-1998 10 1200
6 1997-1998 5 1000
7 1997-1998 20 1300
8 1998-1999 10 2000
9 1998-1999 20 700
and again with 1998 in say H1, you could use the following:-
=SUMPRODUCT((--RIGHT(A2:A9,4)=H1)*C2:C9)
This way you could just change the year in cell H1 and you would see the different values
Pivot Tables are also a good way of summarising this kind of data:-
http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm
If I'm way off the mark here then post back and try and correct my assumptions.
