Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    summing values using vlookup (2002 xp)

    I have a question on how to sum data using a vlookup range.

    Problem:
    I am using daily precipitation values. I would like to enter a beginning date in cell B2 and an ending date in B3. I would like to use vlookup to sum the daily precipitation on those dates (inclusive). For example, suing the following date set, if I enter 8/19/03 as the beginning date and 8/25/03 as the ending date the result should be 2.63.

    8/18/03 0.02
    8/19/03 1.19
    8/20/03 0.58
    8/21/03 0.05
    8/22/03 0.34
    8/23/03 0.01
    8/24/03 0.24
    8/25/03 0.22

    My work-around is to create a cumulative precipitation column and subtract using the following formula.
    =(VLOOKUP(B3,d_ppt,3,FALSE)-VLOOKUP(B2-1,d_ppt,3,FALSE))

    It works, but I was wondering if there a more efficient way to achieve this without adding a cumulative precipitation column.

    Thanks.

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

    Re: summing values using vlookup (2002 xp)

    Say that your data are in D2:E100. You can use this formula:
    <code>
    =SUMIF(D2100,">="&B2,E2:E100)-SUMIF(D2100,">"&B3,E2:E100)
    </code>
    or this one:
    <code>
    =SUMPRODUCT((D2100>=B2)*(D2100<=B3)*E2:E100)</code>

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: summing values using vlookup (2002 xp)

    Hans:

    Once I parsed through the sumif, I liked the logic. Once again, I've learned a little more Excel.
    thanks for this and other help you have given.

    John

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: summing values using vlookup (2002 xp)

    Hans:

    One other question.

    This formula will be used for different data sets, all of which will be in chronological order. Is there a way to have the sumif formula return an error message of some sort to flag whether the looked up date is less than or greater than the data set date range? The vlookup FALSE statement would tell me if the B2 value (looked up value) is not within the data set; e.g. in the example data set a date before 8/18/03 or after 8/25/03. Is there a way to replicate this?

    Thanks again.

    John

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

    Re: summing values using vlookup (2002 xp)

    You could add a test. Using the same assumptions as above:

    =IF(OR(B2>MAX(D2100),B3<MIN(D2100)),"You *** fool",SUMIF(D2100,">="&B2,E2:E100)-SUMIF(D2100,">"&B3,E2:E100))

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: summing values using vlookup (2002 xp)

    I messed around a bit a came up with:

    =IF(OR(B15<MIN('daily precipitation'!A:A),B15>MAX('daily precipitation'!A:A)),"error",SUMIF('daily precipitation'!A:A,">="&B15,'daily precipitation'!B:[img]/forums/images/smilies/cool.gif[/img]-SUMIF('daily precipitation'!A:A,">"&B16,'daily precipitation'!B:[img]/forums/images/smilies/cool.gif[/img])

    Will using the general A:A or B:B statements give a different result than if I hard code the beginning and ending rows?

    (Like your error statement. I use dumb ***k. <g>)
    John

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

    Re: summing values using vlookup (2002 xp)

    You should be okay using entire columns - MIN, MAX and SUMIF ignore blank cells.

Posting Permissions

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