# microsoft excel - How can I sum values within a specific time and date range?

2014-04

I'm trying to work out which formula to use for summing values within a period of time and date. I have the following data:

```
A B C
Jan 1, 2011 8:00 AM 10
Jan 1, 2011 10:00 PM 15
jan 2, 2011 8:30 AM 20
Jan 2, 2011 9:00 AM 15
Jan 3, 2011 8:25 AM 11
Jan 3, 2011 9:00 PM 10
```

I need to add all values in column C which correspond to a certain date and time interval, say, `Jan 1, 2011 9:00 AM to Jan 3, 9:00 AM`

. How can I do this?

You'll need to use `SUMPRODUCT`

instead of `SUMIF`

since you need to satisfy multiple criteria/conditions for the sum.

Assuming the values in column **A** are dates and not strings, this is the formula you need:

```
=SUMPRODUCT((dates+times>=start_date+start_time)*values_to_add)-SUMPRODUCT((dates+times>end_date+end_time)*values_to_add)
```

Here it is in action, using the data range you provided as an example:

If you merge columns **A** & **B**, you'll only need a shorter formula than the one I previously mentioned. You could also use Excel's **Advanced Filtering** tool and then obtain a subtotal of the filtered cells.

You could add a subtotal cell at the end of column c, then create a table out of your data, and filter using only the dates you want. =sumif() is probably faster, but may be less flexible in the end.

This will work without an array formula, letting I1 be the earlier date, and J1 be the later date:

```
=SUMPRODUCT((A1:A6+B1:B6 >= I1)*(A1:A6+B1:B6 <= J1)*C1:C6)
```

If you want to use an array formula, then you can just use a SUM:

```
{=SUM(IF((A1:A6+B1:B6 >= I1)*(A1:A6+B1:B6 <= J1),C1:C6,0))}
```

but I'd never use an array if I didn't need to.

A team of our uses an Excel spreadsheet for their timesheets and uses the 12-hour format.

When inputting time worked if the time is says 2:15 PM - 1:30pm, the sum should be :45 however, for some reason Excel will only show it as 12:45.

This isn't an AM/PM problem as I've triple checked that already.

Any ideas?

Format the result cell as "General" and multiply the result by 1440.