Excel – How to sum values within a specific time and date range

microsoft excel

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?

Best Answer

  • 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.

  • Related Question