# pick from a list of dates and bring over next column

1. ## 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. ## 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

3. ## 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.

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

Attached is a file that has my "problem"

5. ## 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. ## Re: pick from a list of dates and bring over next column

Originally Posted by JBeaucaire
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. ## 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))

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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