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

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.

• nice! I didn't know about array formulas! +1 – kokbira Jun 16 '11 at 14:01
• Generally, one of the advantages of SUMPRODUCT is that you don't have to use array formulas. It can handle ranges like an array formula, very powerful. – Lance Roberts Jun 20 '11 at 16:38

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.

• Great! +1 for providing a shorter formula. You were right about SUMPRODUCT not needing arrays too. – Ellesa Jun 20 '11 at 20:54
• Is there any way to make this work when the table may contain empty rows? I get a #VALUE error if I add an empty row to my table. – Harvey Mar 23 '13 at 5:40