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.

Date	Food	Beverage
26/02/2018 10:28 <tbody> </tbody>		$7.60
26/02/2018 10:37	$13.60
26/02/2018 12:38		$5.80
26/02/2018 1:02	$15.40
26/02/2018 18:30		$30.00
26/02/2018 19:20	$50.00
BREAKFAST	=SUMIF(A:A,A2>"06:00"&A2<"11:00",B:B)	=SUMIF(A:A,A2>"06:00"&A2<"11:00",C:C)
LUNCH	=SUMIF(A:A,A2>"11:00"&A2<"16:00",B:B)	=SUMIF(A:A,A2>"11:00"&A2<"16:00",C:C)
DINNER	=SUMIF(A:A,A2>"16:00"&A2<"00:00",B:B)	=SUMIF(A:A,A2>"16:00"&A2<"00:00",C:C)

<tbody>
</tbody>

I have tried a few other functions but returning to value of a blank timestamp //

00/01/1900 00:00

<tbody>
</tbody>

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?