VLOOUP time range

db49ers

Board Regular
Joined
Oct 13, 2002
Messages
97
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
something like this?

TimeSold________StartHourEndHourSold
22:41:00​
2​
22:00:00​
23:00:00​
4​
14:42:00​
1​
14:00:00​
15:00:00​
2​
16:36:00​
1​
16:00:00​
17:00:00​
2​
20:02:00​
1​
20:00:00​
21:00:00​
3​
20:42:00​
1​
22:07:00​
2​
20:26:00​
1​
14:06:00​
1​
16:51:00​
1​
 
Upvote 0
How about


Excel 2013/2016
ABCDE
1TIMEItems Sold
210:41:00 PM212:00 AM0
32:42:00 PM11:00 AM0
44:36:00 PM12:00 AM0
58:02:00 PM13:00 AM0
68:42:00 PM14:00 AM0
710:07:00 PM25:00 AM0
88:26:00 PM16:00 AM0
92:06:00 PM17:00 AM0
104:51:00 PM18:00 AM0
119:00 AM0
1210:00 AM0
1311:00 AM0
1412:00 PM0
151:00 PM0
162:00 PM2
173:00 PM0
184:00 PM2
195:00 PM0
206:00 PM0
217:00 PM0
228:00 PM3
239:00 PM0
2410:00 PM4
2511:00 PM0
unknown
Cell Formulas
RangeFormula
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))
 
Upvote 0
both are ok :)
but mine require PowerQuery (Get&Transform)

Code:
[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]
 
Upvote 0
For 2003...


Excel 2010
ABCDEF
1TimeSold________StartHourEndHourSold
222:41:00222:00:0023:00:004
314:42:00114:00:0015:00:002
416:36:00116:00:0017:00:002
520:02:00120:00:0021:00:003
620:42:001
722:07:002
820:26:001
914:06:001
1016:51:001
Sheet1
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top