I am trying to add sales that happened daily by time period.
I used sumifs to get sales by date, but I can’t figure it out by day and time period.
For example:
Sales from 8am -12:00 pm, 12-5 pm on 11/20/21
Add a comment
|
1 Answer
QUERY
Sales from 8am -11:59 am on 11/20/21
=query({A2:C8},"select sum(Col3) where Col1 >= datetime '2021-11-20 08:00:00' and Col1 <= datetime '2021-11-20 11:59:59' label sum(Col3) '' ",0)
Sales from 12:00pm - 04:59 pm on 11/20/21
=query({A2:C8},"select sum(Col3) where Col1 >= datetime '2021-11-20 12:00:00' and Col1 <= datetime '2021-11-20 16:59:59' label sum(Col3) '' ",0)
SUMIFS
Sales from 8am -11:59 am on 11/20/21
=sumifs(C2:C,A2:A,">=20/11/2021 08:00:00", A2:A,"<=20/11/2021 11:59:00")
Sales from 12:00pm - 04:59 pm on 11/20/21
=sumifs(C2:C,A2:A,">=20/11/2021 12:00:00", A2:A,"<=20/11/2021 16:59:00")
Note:
You asked for "Sales from 8am -12:00 pm, 12-5 pm on 11/20/21".
If there was a sale at 12:00pm, then it would be included in both calculations. So, you need to define the start or end time so that it is not duplicated in each formula.
Sample
