Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
What have you tried so far? –  jonsca Jun 16 '11 at 11:13

3 Answers 3

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:

enter image description here

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.

share|improve this answer
    
nice! I didn't know about array formulas! +1 –  kokbira Jun 16 '11 at 14:01
1  
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.

share|improve this answer

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.