# COUNT Formula Help

#### KuraiChikara

##### Board Regular
Hello,
I need to find out how many calls come in, for any given day in a month from 7a-7p,.
Criteria is in hour increments 7a-8a, 8a-9a, 9a-10a, 10a-11a, 11a-12p, 12p-1p, 1p-2p, 2p-3p, 3p-4p, 4p-5p, 5p-6p, 6p-7p (12 possible groupings).

Example: if I had calls from 07:01, 07:59, 08:00 and 08:59 all come in 03/26/2019 that would be counted as 4 calls for that day between the range of 7a-8a.

Data:
A2 would contain the date format IE 03/01/2019, 03/02/2019 ' ' ' ' ' ' '03/26/2019, etc
B2 would contain random times from 07:00 to 19:00 IE 07:59, 10:13, 19:59, etc
C3 would have the formula to give me a counted result with the 12 possible grouping criteria that I mentioned which I don't know how to do

I don't know what formula would make this easiest to accomplish, I was thinking =LOOKUP but I'm not sure that will help me. I know I could do a possible nested count if or count ifs but that'll make my sheet large. I'm hopefully looking for a simpler method.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### RasGhul

##### Well-known Member
Hi Kurai,

Can you use this?

#### etaf

##### Well-known Member
use a countifs for each hour range

=COUNTIFS(B2:B1000," > = "&TIMEVALUE("7:00"),B2:B1000," < = "&TIMEVALUE("19:00"),A2:A1000,DATEVALUE("27/3/18"))
where B2:B1000 is the range of times
A2:A1000 is the dates
Change these to suite your data range

Date I have hardcoded but you could change that to reference a particular cell - so the count changes based on the date in that cell

groups
Is this a typo
Example: if I had calls from 07:01, 07:59, 08:00 and 08:59 all come in 03/26/2019 that would be counted as 4 calls for that day between the range of 7a-8a.

8:59 would be a different hour group
If you want each hour group
then

=COUNTIFS(B2:B1000," > ="&TIMEVALUE("7:00"),B2:B1000," < "&TIMEVALUE("8:00"),A2:A1000,DATEVALUE("27/3/18"))
=COUNTIFS(B2:B1000," > ="&TIMEVALUE("8:00"),B2:B1000," < "&TIMEVALUE("9:00"),A2:A1000,DATEVALUE("27/3/18"))

repeat for each hour
NOT this will NOT include 19:00 . as i just have less than
so the last group
=COUNTIFS(B2:B1000," > ="&TIMEVALUE("18:00"),B2:B1000," < ="&TIMEVALUE("19:00"),A2:A1000,DATEVALUE("27/3/18"))
changes to < =

Replies
1
Views
63
Replies
3
Views
84
Replies
1
Views
43
Replies
13
Views
654
Replies
0
Views
45

1,109,359
Messages
5,528,228
Members
409,809
Latest member
VICKRAM

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...