+ Reply to Thread
Results 1 to 5 of 5

select date range in column

  1. #1
    Biff
    Guest

    Re: select date range in column

    Hi!

    Try this:

    D1 = start date
    E1 = end date

    Sum values in column B where the corresponding dates in column A fall within
    the date range (inclusive):

    =SUMIF(A1:A20,">="&D1,B1:B20)-SUMIF(A1:A20,">"&E1,B1:B20)

    Or:

    =SUMPRODUCT(--(A1:A20>=D1),--(A1:A20<=E1),B1:B20)

    Biff

    "garlocd" <[email protected]> wrote in message
    news:[email protected]...
    >I need to be able to sum one column based on dates in another column.
    >
    > Thankful for any help on this.




  2. #2
    Max
    Guest

    Re: select date range in column

    "garlocd" wrote:
    > I need to be able to sum one column based on dates in another column.


    One way ..

    Assuming real dates are within $A$2:$A$100,
    with values to be summed in $B$2:$B$100

    Try something like this in say, C1:
    =SUMPRODUCT(($A$2:$A$100>= -- "1-Jun-2006")*($A$2:$A$100<= --
    "15-Jun-2006"),$B$2:$B$100)

    Adapt to suit, but note that entire col references eg: A:A, B:B, cannot be
    used in SUMPRODUCT
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

  3. #3
    garlocd
    Guest

    select date range in column

    I need to be able to sum one column based on dates in another column.

    Thankful for any help on this.

  4. #4
    Biff
    Guest

    Re: select date range in column

    Hi!

    Try this:

    D1 = start date
    E1 = end date

    Sum values in column B where the corresponding dates in column A fall within
    the date range (inclusive):

    =SUMIF(A1:A20,">="&D1,B1:B20)-SUMIF(A1:A20,">"&E1,B1:B20)

    Or:

    =SUMPRODUCT(--(A1:A20>=D1),--(A1:A20<=E1),B1:B20)

    Biff

    "garlocd" <[email protected]> wrote in message
    news:[email protected]...
    >I need to be able to sum one column based on dates in another column.
    >
    > Thankful for any help on this.




  5. #5
    Max
    Guest

    Re: select date range in column

    "garlocd" wrote:
    > I need to be able to sum one column based on dates in another column.


    One way ..

    Assuming real dates are within $A$2:$A$100,
    with values to be summed in $B$2:$B$100

    Try something like this in say, C1:
    =SUMPRODUCT(($A$2:$A$100>= -- "1-Jun-2006")*($A$2:$A$100<= --
    "15-Jun-2006"),$B$2:$B$100)

    Adapt to suit, but note that entire col references eg: A:A, B:B, cannot be
    used in SUMPRODUCT
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

+ 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