Attempting to Add sums together based off Time Stamp.

[_Melvin_]

Hello Excelsior's!

I am trying to write a formula to collect a subtotal of sales based off time periods. EG: Breakfast 6am - 11am // Lunch 12:00pm - 15:00pm // Dinner 17:00 - 00:00

Giving me FOOD & BEVERAGE total for the above periods

I was going down the Line of using a SUMIF function.

=SUMIF(A:A,A2>"06:00"&A2<"11:00",C:C)

I know i may have to use the TEXT(A2,HH:MM) to format to the time stamp only, however if i can avoid no changing the format at all. That would be an advantage.



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Food[/TD]
[TD]Beverage[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD]26/02/2018 10:28[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]$7.60[/TD]
[/TR]
[TR]
[TD]26/02/2018 10:37[/TD]
[TD]$13.60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/02/2018 12:38[/TD]
[TD][/TD]
[TD]$5.80[/TD]
[/TR]
[TR]
[TD]26/02/2018 1:02[/TD]
[TD]$15.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/02/2018 18:30[/TD]
[TD][/TD]
[TD]$30.00[/TD]
[/TR]
[TR]
[TD]26/02/2018 19:20[/TD]
[TD]$50.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BREAKFAST[/TD]
[TD]=SUMIF(A:A,A2>"06:00"&A2<"11:00",B:B)[/TD]
[TD]=SUMIF(A:A,A2>"06:00"&A2<"11:00",C:C)[/TD]
[/TR]
[TR]
[TD]LUNCH[/TD]
[TD]=SUMIF(A:A,A2>"11:00"&A2<"16:00",B:B)[/TD]
[TD]=SUMIF(A:A,A2>"11:00"&A2<"16:00",C:C)[/TD]
[/TR]
[TR]
[TD]DINNER[/TD]
[TD]=SUMIF(A:A,A2>"16:00"&A2<"00:00",B:B)[/TD]
[TD]=SUMIF(A:A,A2>"16:00"&A2<"00:00",C:C)[/TD]
[/TR]
</tbody>[/TABLE]


I have tried a few other functions but returning to value of a blank timestamp // [TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111"]00/01/1900 00:00[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be awesome!

My first Post, so apologies if my etiquette is off!

_melvin_

 

[sheetspread]

If the stamp is a number, use mod() to get the time, if it's text, use mid() and timevalue(). You'll also need sumproduct instead of sumifs (array vs range).

That's a formula solution, but have you considered the pivot table?