+ Reply to Thread
Results 1 to 14 of 14

Bring back value based on date

  1. #1
    Forum Contributor
    Join Date
    05-13-2006
    Location
    League City, TX
    MS-Off Ver
    2010
    Posts
    139

    Bring back value based on date

    Hello all,
    I'm trying to help someone out with a formula to bring back a value based on a certain company and a certain date.
    sheet 1 is where im trying to bring back value to its certain date.
    project invoice log is where all the information is located.
    example:
    project invoice log:
    Sage Environmental(I5)- $8093.10(M5) - 03/25/10(O5)
    sheet 1
    Sage Environmental(A3) - $8093.10(AO3)

    Hope I explained it enough.
    thx in advance
    mrggutz

    I have attached a sample workbook
    Attached Files Attached Files
    Last edited by mrggutz; 11-02-2010 at 09:15 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Bring back value based on date

    I'm not sure exactly what you want, but if I'm right your workbook layout and proposed method will be cumbersome. I suggest you look at how your source data is laid out & consider using a PivotTable
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    05-13-2006
    Location
    League City, TX
    MS-Off Ver
    2010
    Posts
    139

    Re: Bring back value based on date

    I was trying to use something like this but its not working.
    Please Login or Register  to view this content.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,701

    Re: Bring back value based on date

    Assuming that you are (both) working in Excel 2003, the SUMPRODUCT function cannot handle full columns. Excel 2007 can but it's not recommended.

    So, first step would be to change I:I to, say, I5:I1000. Similarly, U:U and M:M. The "–" should be "--" without quotes.

    The formula would look something like this:

    Please Login or Register  to view this content.

    As RoyUK has said, it looks as though it may be difficult to generalise this.

    Regards
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Contributor
    Join Date
    05-13-2006
    Location
    League City, TX
    MS-Off Ver
    2010
    Posts
    139

    Re: Bring back value based on date

    I tried that and it just brought back a "0" value. I will keep working on it
    thx

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,701

    Re: Bring back value based on date

    If you tried it, why wasn't that in the sample you uploaded?

    Where do you intend to put the formula?

    I put this in cell W5: [CODE=SUMPRODUCT(--(I5:I1000="Sage Environmental"),--(U5:U1000=U5),(M5:M1000)) [/CODE]


    and dragged down; these were the results in W5 to W10

    HTML Code: 
    8093.1
    17964.69
    20101.83
    20101.83
    10488.44
    7746.04

    Regards
    Last edited by TMS; 11-02-2010 at 08:24 AM.

  7. #7
    Forum Contributor
    Join Date
    05-13-2006
    Location
    League City, TX
    MS-Off Ver
    2010
    Posts
    139

    Re: Bring back value based on date

    This worked in 2007 version using "Sumifs" but I know it will not work in 2003 version, what can I use instead in 03?
    Please Login or Register  to view this content.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,701

    Re: Bring back value based on date

    Sumproduct

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,701

    Re: Bring back value based on date

    I note that in your SUMIFS, you are comparing something in A2 to column I.

    In the sample, A2 has a heading, A3 has OC-08 and column I has the Vendor names.

    We may, therefore, be talking at cross purposes.

    I'm sure that's as frustrating for you as it is for me.

    Regards
    Last edited by TMS; 11-02-2010 at 08:36 AM.

  10. #10
    Forum Contributor
    Join Date
    05-13-2006
    Location
    League City, TX
    MS-Off Ver
    2010
    Posts
    139

    Re: Bring back value based on date

    I just tried it this morning at home, the sample is from yesterday sry.
    I plan to use the formula in sheet 1 under it appropriate date.
    I'm gonna try what you did and I will let you know how it comes out thx.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,701

    Re: Bring back value based on date

    In cell C3 on Sheet1 (in your sample workbook)

    Please Login or Register  to view this content.

    returns

    HTML Code: 
    20101.83

    I think this demonstrate the principle; you need to adjust the ranges and search values accordingly.

    Regards

  12. #12
    Forum Contributor
    Join Date
    05-13-2006
    Location
    League City, TX
    MS-Off Ver
    2010
    Posts
    139

    Re: Bring back value based on date

    Thank you very much that works I will adjust where needed.
    thanks again have a good one.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,701

    Re: Bring back value based on date

    You're welcome. Thanks for the feedback.

  14. #14
    Forum Contributor
    Join Date
    05-13-2006
    Location
    League City, TX
    MS-Off Ver
    2010
    Posts
    139

    Re: Bring back value based on date

    Hey no problem I got it all working great now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1