0

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

1 Answer 1

0

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

snapshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.