Calculating time range

**joeycrak** · 07-11-2013, 11:12 AM

I'm trying to find how many records i have for certain time ranges. I need counts for before 10:00 between 10:00 and 13:00 and after 15:30. i have only two columns in my pivot table. Column B is my count for how many orders i have for that time.

Column A4:A58 Column B4:B58
08:15:00 1
08:16:00 8
08:20:00 18
08:22:00 3
08:23:00 137
08:24:00
08:25:00
08:43:00
08:44:00
08:51:00
08:52:00
08:56:00
08:57:00
08:58:00
09:05:00
09:16:00
09:20:00
09:21:00
09:31:00
09:42:00
09:49:00
10:15:00
10:46:00
11:48:00
12:03:00
13:22:00
13:23:00
13:24:00
13:28:00
13:31:00
13:36:00
13:38:00
13:50:00
14:30:00
15:14:00
15:15:00
15:16:00
15:17:00
15:18:00
15:19:00
15:20:00
15:21:00
15:23:00
15:24:00
15:25:00
16:01:00
16:02:00
16:13:00
16:14:00
16:36:00
16:38:00
16:39:00
16:44:00
16:45:00
17:06:00

**dilipandey** · 07-11-2013, 12:21 PM

HI Joeycrak,

You can use sumproduct function here :-

Formula:

Please Login or Register  to view this content.

where d2 and e3 can be time in between you need to calculate the count, see below file for more :-

time range.xlsx

Regards,
DILIPandey
<click on below * if this helps>

**Pete_UK** · 07-11-2013, 12:22 PM

Are you saying that the counts for each time are in column B, and you want to SUM these within your 3 ranges?

Also, does nothing happen between 13:00 and 15:30, or did you leave that range off your description?

Pete

**etaf** · 07-11-2013, 12:30 PM

perhaps a sample spreadsheet attached may help

countif() or countifs() should help

**joeycrak** · 07-11-2013, 02:00 PM

Here you go

**etaf** · 07-11-2013, 02:03 PM

here i have added the value again in the data
and then click on the entry

Value field settings
show values as
% of grand total

**joeycrak** · 07-11-2013, 02:13 PM

yes i need to figure out my counts for my time ranges. example from my initial post if i wanted to know 08:00:00-08:20:00 would be 27 and 08:20:00-08:23:00 would be 140

08:00:00-10:00:00
10:00:00-13:00:00
13:00:00-13:50:00
13:50:00-17:30:00