CODEWITHSUNDEEP
excelformulasumifs
Prabhat Vishwas
Prabhat Vishwas

Reputation: 43

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

Upvotes: 0

Views: 452

Answers (1)

teylyn
teylyn

Reputation: 35915

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)))

Upvotes: 2

Related Questions