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

25
2014-04
  • Cheng

    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?

  • Answers
  • Ellesa

    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.

  • Kirk

    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.

  • Lance Roberts

    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.


  • Related Question

    Subtracting time values in Excel - If sum is less than one hour it automatically adds 12 hours to sum
  • Windows Ninja

    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?


  • Related Answers
  • Carl

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

    enter image description here