How can I sum values within a specific time and date range?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.