+ Reply to Thread
Results 1 to 7 of 7

pick from a list of dates and bring over next column

  1. #1
    Registered User
    Join Date
    04-25-2005
    Posts
    11

    pick from a list of dates and bring over next column

    Hello! In column B I have a list of dates from 11/1/09 through 8/28/10. In column C I have various dollars associated with costs accumulated on those dates.
    Below this data I have a listed in columns B through M each month of the year and above the actual text, a start and end date for each month.

    What I would like to do is do a search on the column of dates, and if the search finds, for example, 2/11/10, I would like to pull the dollar value from column C, and put it in the February cell below. If there are multiple values in February, I would like them to add up to one number for all costs in Feb.

    Right now I am trying the AND function and comparing the date in B2 to the start and end dates for the months. The problem is that I have to review hundreds of dates, and feel like with this approach I have to have a formula for each cell with a date in it.

    Any thoughts on how I could make this easier??

    Thanks!!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,331

    Re: pick from a list of dates and bring over next column

    Hi,

    Can you upload your workbook along with a note of example values of what you expect to see and where?

    Rgds
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: pick from a list of dates and bring over next column

    Probably some sort of SUMPRODUCT() formula:

    =SUMPRODUCT(--($B$1:$B$200>=StartDate), --($B$1:$B$200<=EndDate), $C$1:$C$200)

    Adjust the red values to your cells with dates.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    04-25-2005
    Posts
    11

    Re: pick from a list of dates and bring over next column

    Attached is a file that has my "problem"
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-25-2005
    Posts
    11

    Re: pick from a list of dates and bring over next column

    I worked on it and the SUMPRODUCT was great. Thanks for the help!!

  6. #6
    Registered User
    Join Date
    01-25-2010
    Location
    Tucson, AZ
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: pick from a list of dates and bring over next column

    Quote Originally Posted by JBeaucaire View Post
    Probably some sort of SUMPRODUCT() formula:

    =SUMPRODUCT(--($B$1:$B$200>=StartDate), --($B$1:$B$200<=EndDate), $C$1:$C$200)

    Adjust the red values to your cells with dates.

    I'm fairly new to using formulas in excel and I'm trying to learn all that I can. I know that you are really busy but can you show exactly what the formula would look like based on the example worksheet padt7260 attatched. I'm not sure what "Adjust the red values to your cells with dates" means. Thank you

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: pick from a list of dates and bring over next column

    Your sheet is horribly non-standard in its layout. Typically you would have ONE column with dates, another with values.

    However, SUMPRODUCT() will still work, you just need one the many syntax variations available.

    Put this in B35 and copy across:

    =SUMPRODUCT(($A$1:$E$31>=B32)*($A$1:$E$31<=B33)*($B$1:$F$31))

+ 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