count area & give total

[shital]

i have a data like
year Pur. for year Area
1996-1997 5 1300
1996-1997 10 350
1996-1997 15 290
1997-1998 10 1200
1997-1998 5 1000
1997-1998 20 1300
1998-1999 10 2000
1998-1999 20 700


i want to know if i type year 2003 how much area i have as
on todays date:-
example:- i type 2003, pur. for year is over it should
not count area of that and than it should give the total
of area.

Shital
..

 

[Ken Wright]

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,D29)

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.

 

[Bernard V Liengme]

Hi Shital,
You need to be clearer. There is no year 2003 in the data. What is the
meaning of the second column? In the first row the 'year' is 1996 and 'pur
for year' is 5; do we assume this means the area was purchased for 1996+5 =
2001?
Bernard