VLOOUP time range

[db49ers]

I'm trying to count how many items have been sold each hour from a report. In example below between 10:00pm and 11:00pm I sold 4 items. I need to do this for each hour. How do I do a lookup for a time range? Thanks in advance!
TIME Items Sold
10:41:00 PM 2
2:42:00 PM 1
4:36:00 PM 1
8:02:00 PM 1
8:42:00 PM 1
10:07:00 PM 2
8:26:00 PM 1
2:06:00 PM 1
4:51:00 PM 1

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Time[/td][td=bgcolor:#5B9BD5]Sold[/td][td]________[/td][td=bgcolor:#70AD47]StartHour[/td][td=bgcolor:#70AD47]EndHour[/td][td=bgcolor:#70AD47]Sold[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

22:41:00​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]

22:00:00​

[/td][td=bgcolor:#E2EFDA]

23:00:00​

[/td][td=bgcolor:#E2EFDA]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

14:42:00​

[/td][td]

1​

[/td][td][/td][td]

14:00:00​

[/td][td]

15:00:00​

[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

16:36:00​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]

16:00:00​

[/td][td=bgcolor:#E2EFDA]

17:00:00​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20:02:00​

[/td][td]

1​

[/td][td][/td][td]

20:00:00​

[/td][td]

21:00:00​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

20:42:00​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

22:07:00​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

20:26:00​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

14:06:00​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

16:51:00​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E
1	TIME	Items Sold
2	10:41:00 PM	2		12:00 AM	0
3	2:42:00 PM	1		1:00 AM	0
4	4:36:00 PM	1		2:00 AM	0
5	8:02:00 PM	1		3:00 AM	0
6	8:42:00 PM	1		4:00 AM	0
7	10:07:00 PM	2		5:00 AM	0
8	8:26:00 PM	1		6:00 AM	0
9	2:06:00 PM	1		7:00 AM	0
10	4:51:00 PM	1		8:00 AM	0
11				9:00 AM	0
12				10:00 AM	0
13				11:00 AM	0
14				12:00 PM	0
15				1:00 PM	0
16				2:00 PM	2
17				3:00 PM	0
18				4:00 PM	2
19				5:00 PM	0
20				6:00 PM	0
21				7:00 PM	0
22				8:00 PM	3
23				9:00 PM	0
24				10:00 PM	4
25				11:00 PM	0
unknown

Cell Formulas
Range	Formula
D2	=TIME(ROW()-2,0,0)
E2	=SUMIFS($B$2:$B$10,$A$2:$A$10,">="&TIME(ROW()-2,0,0),$A$2:$A$10,"<"&TIME(ROW()-1,0,0))

 

[db49ers]

Exactly that!

 

[sandy666]

which one ?

 

[db49ers]

which one ?

Yours please! Thanks for the other answer too!

 

[sandy666]

both are ok
but mine require PowerQuery (Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Sold", Int64.Type}}),
    Duplicate = Table.DuplicateColumn(Type, "Time", "Time - Copy"),
    Reorder = Table.ReorderColumns(Duplicate,{"Time", "Time - Copy", "Sold"}),
    Rename = Table.RenameColumns(Reorder,{{"Time", "Start"}, {"Time - Copy", "End"}}),
    Start = Table.TransformColumns(Rename,{{"Start", Time.StartOfHour, type time}}),
    End = Table.TransformColumns(Start,{{"End", Time.EndOfHour, type time}}),
    Group = Table.Group(End, {"Start", "End"}, {{"Sold", each List.Sum([Sold]), type number}})
in
    Group[/SIZE]

 

[db49ers]

Wow! Above my pay grade! Still using Excel 2003!

 

[sandy666]

Pity, but the life goes on

 

[MARK858]

For 2003...

Excel 2010
	A	B	C	D	E	F
1	Time	Sold	________	StartHour	EndHour	Sold
2	22:41:00	2		22:00:00	23:00:00	4
3	14:42:00	1		14:00:00	15:00:00	2
4	16:36:00	1		16:00:00	17:00:00	2
5	20:02:00	1		20:00:00	21:00:00	3
6	20:42:00	1
7	22:07:00	2
8	20:26:00	1
9	14:06:00	1
10	16:51:00	1
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(($A$2:$A$10>= $D2)*($A$2:$A$10 <= $E2),$B$2:$B$10)
F3	=SUMPRODUCT(($A$2:$A$10>= $D3)*($A$2:$A$10 <= $E3),$B$2:$B$10)
F4	=SUMPRODUCT(($A$2:$A$10>= $D4)*($A$2:$A$10 <= $E4),$B$2:$B$10)
F5	=SUMPRODUCT(($A$2:$A$10>= $D5)*($A$2:$A$10 <= $E5),$B$2:$B$10)