pick from a list of dates and bring over next column

**patd7260** · 01-25-2010, 06:10 PM

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

**Richard Buttrey** · 01-25-2010, 06:14 PM

Hi,

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

Rgds

**JBeaucaire** · 01-25-2010, 06:37 PM

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.

**patd7260** · 01-26-2010, 09:29 AM

Attached is a file that has my "problem"

**patd7260** · 01-26-2010, 10:39 AM

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

**krie2509** · 01-26-2010, 12:03 PM

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

**JBeaucaire** · 01-26-2010, 12:36 PM

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