Counting a Time Range with COUNTIFS or SUMIFS

Josh08

New Member
Good Morning

I have an issue. I've been trying to create a formula to count the number of instances a particular time range appears in a column on a spreadsheet.

Column C contains manually entered times followed by AM or PM. One problem I've run into that may or may not be effecting the formula's I've tried is that the time formats are different in some columns.

Example
Data Type = General
12:17 PM

Example
Data Type = Custom
12:02:00 PM

I've tried many methods to make them all the same format. I would prefer the one with the General data type.

I tried converting the data type. Copying and pasting one with the format I wanted and change it in the cell, no luck.

=SUMIFS(A:A,">=6:00AM", A:A, "<3:00PM")

My desired results were not yielded from that formula. I've tried other formulas without the correct result either. I don't want to manually count over 350 times.
 

James006

Well-known Member
Hello,

The main question is to determine if you are dealing with Text or dealing with Numbers ...
 

DanteAmor

Well-known Member
Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:136.87px;" /><col style="width:38.02px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td >TIME</td><td > </td><td >Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td style="text-align:right; ">05:20:00 a.m.</td><td > </td><td style="text-align:right; ">6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">06:45:00 a.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">08:10:00 a.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">09:35:00 a.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">11:00:00 a.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">12:25:00 p.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">01:50:00 p.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="text-align:right; ">03:15:00 p.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td style="text-align:right; ">04:40:00 p.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td style="text-align:right; ">06:05:00 p.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td style="text-align:right; ">07:30:00 p.m.</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td style="text-align:right; ">08:55:00 p.m.</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=COUNTIFS(C2:C19,">=06:00",C2:C19,"<=15:00")</td></tr></table></td></tr></table>
 

Joe4

MrExcel MVP, Junior Admin
The main question is to determine if you are dealing with Text or dealing with Numbers ...
You can quickly verify with the ISNUMBER function (as dates/times are stored in Excel as numbers).

So, if your time entry is in the cell A1, then enter this formula anywhere:
=ISNUMBER(A1)
If it returns TRUE, you have a valid date/time entry.
If it returns FALSE, you actually have a text entry.

In order to do date/time math on it, you will want it to be a valid date/time entry.
 

Josh08

New Member
You can quickly verify with the ISNUMBER function (as dates/times are stored in Excel as numbers).

So, if your time entry is in the cell A1, then enter this formula anywhere:
=ISNUMBER(A1)
If it returns TRUE, you have a valid date/time entry.
If it returns FALSE, you actually have a text entry.

In order to do date/time math on it, you will want it to be a valid date/time entry.
70 = True
380 = False
 

Josh08

New Member
I still didn't yield the appropriate count. Perhaps it has something to do with them not being numbers? See response below 70 are numbers, 380 aren't.
 

James006

Well-known Member
I still didn't yield the appropriate count. Perhaps it has something to do with them not being numbers? See response below 70 are numbers, 380 aren't.
Are you saying that within your column ... certains cells are Numbers and some others are Text ...???
 

Josh08

New Member
Are you saying that within your column ... certains cells are Numbers and some others are Text ...???

I think I'm almost there. I was able to convert the values to numbers successfully. I now have a count of 370 numeric Time values in Column B of my spreadsheet.

I'm trying to capture Action Requests from 1-3rd shift. However with my COUNTIFS (3 statements) I'm short by 13 total. I have 357 not 370.

1st Shift (6:00AM - 3:00PM) - =COUNTIFS('Request Tracker'!C:C,">6:00",'Request Tracker'!C:C,"<15:00")
2nd Shift (3:00 PM - 10:00PM) - =COUNTIFS('Request Tracker'!C:C,">15:00",'Request Tracker'!C:C,"<22:00")
3rd Shift (10:00PM - 6:00AM) - =COUNTIFS('Request Tracker'!C:C,">22:00",'Request Tracker'!C:C,"<6:00")


1st Shift = 273
2nd Shift = 84
3rd Shift = 0
Total = 357
 

Josh08

New Member
What the totals should be:

1st Shift = 273
2nd Shift = 85
3rd Shift = 12
Total = 370
 

James006

Well-known Member
Without your file .. difficult to guess what is in front of your eyes ...

Most probably for 2nd shift ... you should either >= or <=

and your third shift should be the Total minus both 1st and 2nd Shift ...
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top