+ Reply to Thread
Results 1 to 6 of 6

How do I use LOOKUP to return a range of values, then SUM values?

  1. #1
    irvine79
    Guest

    How do I use LOOKUP to return a range of values, then SUM values?

    Any thoughts? I have spreadsheet that has this years dates running down
    column A. I then have each day's corresponding production figure running down
    column B. I want to assign a function to a cell that will result in the
    months cumulative production figure. (I realize I can manually select the
    desired Column B cells, but this is not the solution I am looking for)

    The formula I have come up with so far is:

    =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm"),B1:B365))

    For some reason the result is 0. I used the formula auditing tool and came
    up with nothing. Any thoughts?

    Thanks

  2. #2
    Registered User
    Join Date
    08-02-2006
    Posts
    3
    Two questions:
    1- why not sum in the talley cell with a simple function?
    2- are you talleying the month or the year?

    it is not clear in your decription, each month then you total the year?

  3. #3
    Fred Smith
    Guest

    Re: How do I use LOOKUP to return a range of values, then SUM values?

    First, you don't need the Lookup, and second, you want Sumif, not Sum.

    However, you're trying to sum based on two selections (greater than the 1st of
    the month and less than today). To get around this, you have two choices:

    1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can
    use:
    =sumif(C:C,text(today(),"mmm"),B:B)

    2. Use Sumproduct, as in:

    =SUMPRODUCT(A1:A365>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),A1:A365<=TODAY(),B1:B365)

    --
    Regards,
    Fred


    "irvine79" <[email protected]> wrote in message
    news:[email protected]...
    > Any thoughts? I have spreadsheet that has this years dates running down
    > column A. I then have each day's corresponding production figure running down
    > column B. I want to assign a function to a cell that will result in the
    > months cumulative production figure. (I realize I can manually select the
    > desired Column B cells, but this is not the solution I am looking for)
    >
    > The formula I have come up with so far is:
    >
    > =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm"),B1:B365))
    >
    > For some reason the result is 0. I used the formula auditing tool and came
    > up with nothing. Any thoughts?
    >
    > Thanks




  4. #4
    Arvi Laanemets
    Guest

    Re: How do I use LOOKUP to return a range of values, then SUM values?

    Hi

    =SUMIF(A1:A365,">=" & TEXT(DATE(2006,n,1),B1:B135) - SUMIF(A1:A365,"<=" &
    TEXT(DATE(2006,n+1,0),B1:B135)
    , or
    =SUMPRODUCT(--(MONTH(A1:A365)=n),B1:B135)

    , where n is the number of month.


    --
    Arvi Laanemets
    ( My real mail address: arvi.laanemets<at>tarkon.ee )



    "irvine79" <[email protected]> wrote in message
    news:[email protected]...
    > Any thoughts? I have spreadsheet that has this years dates running down
    > column A. I then have each day's corresponding production figure running
    > down
    > column B. I want to assign a function to a cell that will result in the
    > months cumulative production figure. (I realize I can manually select the
    > desired Column B cells, but this is not the solution I am looking for)
    >
    > The formula I have come up with so far is:
    >
    > =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm"),B1:B365))
    >
    > For some reason the result is 0. I used the formula auditing tool and came
    > up with nothing. Any thoughts?
    >
    > Thanks




  5. #5
    irvine79
    Guest

    Re: How do I use LOOKUP to return a range of values, then SUM valu

    Fred: You're the man. Thanks for the help. It definitely got me headed in the
    right direction. Any thoughts on how I'd average this months daily production
    totals from the 1st until any given day of the month using the same sumif
    logic?

    thanks

    "Fred Smith" wrote:

    > First, you don't need the Lookup, and second, you want Sumif, not Sum.
    >
    > However, you're trying to sum based on two selections (greater than the 1st of
    > the month and less than today). To get around this, you have two choices:
    >
    > 1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can
    > use:
    > =sumif(C:C,text(today(),"mmm"),B:B)
    >
    > 2. Use Sumproduct, as in:
    >
    > =SUMPRODUCT(A1:A365>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),A1:A365<=TODAY(),B1:B365)
    >
    > --
    > Regards,
    > Fred
    >
    >
    > "irvine79" <[email protected]> wrote in message
    > news:[email protected]...
    > > Any thoughts? I have spreadsheet that has this years dates running down
    > > column A. I then have each day's corresponding production figure running down
    > > column B. I want to assign a function to a cell that will result in the
    > > months cumulative production figure. (I realize I can manually select the
    > > desired Column B cells, but this is not the solution I am looking for)
    > >
    > > The formula I have come up with so far is:
    > >
    > > =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm"),B1:B365))
    > >
    > > For some reason the result is 0. I used the formula auditing tool and came
    > > up with nothing. Any thoughts?
    > >
    > > Thanks

    >
    >
    >


  6. #6
    Fred Smith
    Guest

    Re: How do I use LOOKUP to return a range of values, then SUM valu

    Sure. Just divide by the day of the month, as in:

    =sumif(..)/day(today())

    Of, if you wanted to specify the day, say in c1, your average daily production
    from the 1st to c1 of the month is:

    =SUMPRODUCT(A1:A365>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),A1:A365<=DATE(YEAR(TODAY()),MONTH(TODAY()),C1),B1:B365)/C1

    --
    Regards,
    Fred


    "irvine79" <[email protected]> wrote in message
    news:[email protected]...
    > Fred: You're the man. Thanks for the help. It definitely got me headed in the
    > right direction. Any thoughts on how I'd average this months daily production
    > totals from the 1st until any given day of the month using the same sumif
    > logic?
    >
    > thanks
    >
    > "Fred Smith" wrote:
    >
    >> First, you don't need the Lookup, and second, you want Sumif, not Sum.
    >>
    >> However, you're trying to sum based on two selections (greater than the 1st
    >> of
    >> the month and less than today). To get around this, you have two choices:
    >>
    >> 1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can
    >> use:
    >> =sumif(C:C,text(today(),"mmm"),B:B)
    >>
    >> 2. Use Sumproduct, as in:
    >>
    >> =SUMPRODUCT(A1:A365>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),A1:A365<=TODAY(),B1:B365)
    >>
    >> --
    >> Regards,
    >> Fred
    >>
    >>
    >> "irvine79" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Any thoughts? I have spreadsheet that has this years dates running down
    >> > column A. I then have each day's corresponding production figure running
    >> > down
    >> > column B. I want to assign a function to a cell that will result in the
    >> > months cumulative production figure. (I realize I can manually select the
    >> > desired Column B cells, but this is not the solution I am looking for)
    >> >
    >> > The formula I have come up with so far is:
    >> >
    >> > =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm"),B1:B365))
    >> >
    >> > For some reason the result is 0. I used the formula auditing tool and came
    >> > up with nothing. Any thoughts?
    >> >
    >> > Thanks

    >>
    >>
    >>




+ 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