Search code examples
excelformulasumifs

How to sumifs based on date and time in excel?

How to sum based on date and time?

Dear Respected Team, there are many examples of sumifs based on Date_range on internet but I could not get any formula that how to sumifs based on date_and_time range.

I mean how to Sum Per_Day_Charges from 24-10-2020 20:04:00 to 26-10-2020 01:35:00?

Data are mentioned below.

Date & Time Per Day charges

21-09-2020 18:16:00 100

22-10-2020 12:22:00 100

23-10-2020 11:30:00 100

24-10-2020 19:52:00 100

24-10-2020 20:04:00 100

24-10-2020 23:10:00 100

25-10-2020 06:10:00 100

25-10-2020 16:35:00 100

25-10-2020 17:25:00 100

26-10-2020 01:06:00 100

26-10-2020 01:35:00 100

26-10-2020 03:12:00 100

26-10-2020 03:42:00 100

26-10-2020 03:44:00 100

26-10-2020 03:49:00 100

SUM ONLY PINK BACKGROUND CELLS

Solution

  • Don't overthink this.

    Put the two boundary date/times into two cells and reference these two cells in a Sumifs.

    =SUMIFS(B3:B17,A3:A17,">="&F3,A3:A17,"<="&F4)

    enter image description here

    If you don't want to use cells for the comparison, keep in mind that a date/time value is just a date and a time added together, so you can also use

    =SUMIFS(B3:B17,A3:A17,">="&(DATE(2020,10,24)+TIME(20,4,0)),A3:A17,"<="&(DATE(2020,10,26)+TIME(1,35,0)))