1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2003 - Selecting data within a date range with a further logic field?

Discussion in 'Business Applications' started by Highland Learner, Jul 11, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Highland Learner

    Highland Learner Thread Starter

    Joined:
    Jul 9, 2008
    Messages:
    13
    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
     
  2. Sponsor

  3. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    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?
     
  4. Highland Learner

    Highland Learner Thread Starter

    Joined:
    Jul 9, 2008
    Messages:
    13
    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?
     
  5. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    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?
     
  6. Highland Learner

    Highland Learner Thread Starter

    Joined:
    Jul 9, 2008
    Messages:
    13
    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.
     
  7. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    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:
    Code:
    =SUMPRODUCT(((A7:A18)>=A6)*((A7:A18)<=B6)*(B7:B18)*(D7:D18)<D1)
    
    where D7:D18 is the data rage containing year, D1is the current data or any other year you wanna
     
  8. Highland Learner

    Highland Learner Thread Starter

    Joined:
    Jul 9, 2008
    Messages:
    13
    Tried to email you ...?
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/729483