Count & 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
...


..

 

[Bernard Liengme]

You will need to expalin this more. Since there year 2003, do we assume that
rwo 1 is for 1996+5=2001?
Please rephrase the question; do not keep posting it in the same manner.
Bernard

 

[RSnyder]

By your data I'm assuming that the "year" is the year of
purchase, the "Pur. for year" is the duration or life, and
the "Area" represents a measured area. Assuming that the
1st year indicated is when the area measurement came into
existence, then the area measure in 1996 (1st of your
example) would no longer be applicable in 2002. I think
if that were the case I would isolate would isolate the
data as follows and add a formula to show that ending year
that's applicable:

Your year sought = 2003; held in cell named YEARSOUGHT


A B C D E
1 YEAR LIFE AREA ENDS COMPYEAR
2 1996 5 1300 2001 2001
3 1996 10 350 2006 2003
4 1996 15 290 2011 2003

COMPYEAR is based on a formula (for 1st row of data; =IF
(D2>=YEARSOUGHT,YEARSOUGHT,D2) that returns either the
year your looking for or the year from the "ENDS" column.

The only thing left to do is to insert a formula near
where you put in the year for the YEARSOUGHT range that
gives you the total area. The formula:
=SUMIF(E:E,YEARSOUGHT,C:C)
will return the sum of "AREA" in the worksheet where your
data is at where the COMPYEAR equals the YEARSOUGHT value.
See if that helps.