Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    lookup and sum formula (2003)

    I was wondering if It is possible to create a formula that can lookup values (on the different spreadsheet) and add those values that meet date criteria in the same formula. I was trying to combine VLOOKUP (lookup name is right above the column with values) and SUMIF formulas. However, I was unsuccessfull so far. If someone knows how to do that, please help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup and sum formula (2003)

    Welcome to Woody's Lounge!

    Could you explain in more detail what you want to accomplish, and perhaps attach a small sample workbook that illustrates it?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: lookup and sum formula (2003)

    Perhaps the links in Re: SUMIF with multiple criteria (Excel 2003) could be of use

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    If I understand what you are asking, then assume the following:

    The dates are in A1:A30
    The values to be summed are in B1:B30
    If you want to sum all of the values in B1:B30 where the date in A1:A30 is greater than or equal to the date in F1 and less than or equal to the date in G1, then you could use the following formula:

    <code>
    =SUMPRODUCT((A1:A30>=F1)*(A1:A30<=G1)*B1:B30)
    </code>
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    I want to enter this formula into workbook Summary, cell G14. The data will come from woorkbook Q1, column G. The criteria is date in column E. In workbook Summary, cell G14, I want to have total of all SMI debt for the properties acquired prior to Jan 2002. See attached spreadsheet.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup and sum formula (2003)

    Enter the date 1/1/2002 in (for example) cell G1 on the Q1 sheet. The formula
    <code>
    =SUMIF('Q1'!E7:E22,"<"&'Q1'!G1,'Q1'!G7:G22)
    </code>
    will add the values in G7:G22 for which the date in E7:E22 is before 1/1/2002 (or whichever date is entered in G1).

  7. #7
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    This is a useful formula that I can use instead of SUMIF. My only problem is that I can figure out how to factor it correctly into the Vlookup or other lookup formula.

    alex

  8. #8
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    Thanks Hans!

    Is there a way that this formula can lookup a date in column E? I can't enter this date up at the top (the dates has to stay in column E).

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup and sum formula (2003)

    The formula I proposed does look up dates in column E, but you have to specify the value to compare with (January 1, 2002 in your example) somewhere. It doesn't have to be cell G1 on the Q1 sheet.

  10. #10
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    I figured that also after I emailed you.

    Thanks a lot!

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    I don't think you need any VLOOKUP. Does the formula in the attached do what you want?
    Legare Coleman

  12. #12
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    Thanks! This exactly what I needed: date criteria in the same formula without reference to other cells. I am working this formula that you've send me earlier =SUMPRODUCT(('Q1'!E9:E254>=DATE(2002,1,1))*('Q1'!E 9:E254<=DATE(2002,12,31))*G9:G254) (My range is different in here than in the example I've attached, but it should work anyway). I am missing something in this formula and I can't figure it out. Can you help me?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup and sum formula (2003)

    I think you want
    <code>
    =SUMPRODUCT(('Q1'!E9:E254>=DATE(2002,1,1))*('Q1'!E 9:E254<=DATE(2002,12,31))*'Q1'!G9:G254)
    </code>
    I added 'Q1'! to the last range.

  14. #14
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    Yup! That works! Thanks Hans!

  15. #15
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup and sum formula (2003)

    I am trying to get # of faciliteis opened prior to 1/1/2002. What is wrong with this formula? =COUNTIF('Q1'!A9:A254,'Q1'!E9:E254<DATE(2002,1,1)) it returns zero.

    How do I also get a rage of dates in criteria. It should look something like this =COUNTIF('Q1'!A9:A254,('Q1'!E9:E254>=DATE(2002,1,1 ))*'(Q1'!E9:E254<=DATE(2002,12,31)))

Page 1 of 2 12 LastLast

Posting Permissions

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