Finding Matches within Date Ranges

**jkalfsbeck** · 03-04-2010, 02:55 PM

Hello,

I have a spreadsheet developed for an entire years worth of daily work. I have a starting date for the year and an end date for the year, 10,1,2009 - 9,30,2010.

I am trying to develop a formula that will search the entire year work dates and each time the same date appears will sum a third column that has hours per day assigned to that specific date. I.E. if i have 3 of the same dates spread throughout the year and 2 hours of work assigned for that date it will sum the hours and return 6 (3 days times 2 hours = 6). I want to be able to fill out my year work schedule and use the formula to see if i have over 8 hours assigned per date / day for the entire year. Unfortunately i am not allowed to set my schedule in ascending order, i have to list groups of work for the year and that is requiring me to have the same date appear in several different areas of my year schedule as i only assign a few hours of work to each group that i work on daily to total 8 hours per day.

**NBVC** · 03-04-2010, 03:03 PM

Try Sumif. See Excel help for more.

e.g

=Sumif($A$1:$A$100,X1,$B$1:B$100)

this will sum values in B1:B100 where A1:A100 has same value (date) as entry in X1

**jkalfsbeck** · 03-04-2010, 04:19 PM

Originally Posted by NBVC

Try Sumif. See Excel help for more.

e.g

=Sumif($A$1:$A$100,X1,$B$1:B$100)

this will sum values in B1:B100 where A1:A100 has same value (date) as entry in X1

Tried that and it works for one column of dates, but will not seek dates between a two column date range. Spreadsheet is an example of what I am trying to accomplish. Basically, what I am trying to have a formula do is to search for a single date between a date range even if it shows several times within a date range and total or sum the number of hours associated with that single date. (spreadsheet yellow column)