[Highland Learner]

Hi, I was kindly helped my AJ on this site to end up with

=SUMPRODUCT(((A7:A18)>=A6)*((A7:A18)<=B6)*(B7:B18))

for the folowing:


2 colums, 'A' with the year (currently as plain number) 'B' with a simple number. There are roughtly 5/8 entries for each year, from 1989 to 2008. I need to sum 'b' for a range of years, (say 1990 to 1995), I can do it for one year value but not more.

but the further problem I have is:


I have a list of 160 rows with the year being the key data field and selecting various ranges say 1995 to 2006.

I need to capture the data in a way that I need to keep historic info for years gone (2006,2007) but then alter the sheet (remove a row if no longer relevant) for current and future years.

I was thinking of having a final column with a "year removed field" and the main formula above being dependant on that for inclusion, make sense?


Many thanks

[Aj_old]

Let see if I understood you right.
You wanna the year that are considered to old to be removed from the page and stored elsewhere?

[Highland Learner]

Hi thanks for coming back to me, it is a little tough to explain. No not exactly. This is a list of ships, their year, weight etc. I want historical data (so the size of the fleet etc for 2006, 2007) to remain set in stone. BUT from the same sheet of data (in year order) as new ships come into use I want to add them at the bottom. The problem is, say when one from 2001 is removed (sunk, sold etc) if the data row were to be removed then is would be taken out of the historical data (which it can't as it was there then!) for current and future years it needs to be removed. (to explain there data goes up to 2010 ish as we know of ships that are going to be built.

So I was thinking of having a final field that says, "out of service" which would be blank unless it is removed when the year would be entered. Then the equation you sent me looks in that field?

Make sense?

[Aj_old]

OK
another shut:
You wanna to counted (sumed) data only for those ships that where produced in the period and are in use at the moment?

[Highland Learner]

that is kinda where I believe I am now. can I post the excel sheet here? would you be prepared to take a look? it's nothing that is not in the public domain.

[Aj_old]

is always easier to work when you have a visual example, I mean a sample of the file!
but it's your decision to post a sample or not! If you wanna you can send it to me via mail, I'll take a look, and see what can be done! Or just remove any "personal" info and uppload it here!

You can make a new column named "OutOfSerice", in which you'll inpunt the year (or even the date) ship was removed from use!
And change the formulas:

=SUMPRODUCT(((A7:A18)>=A6)*((A7:A18)<=B6)*(B7:B18)*(D7:D18)<D1)

where D718 is the data rage containing year, D1is the current data or any other year you wanna

[Highland Learner]

Tried to email you ...?