How can I use COUNTIF to find a total after a certain time
I am looking to count the amount of calls received after business hours from an exported sheet. In the below example, we received 6 calls after 5:00 PM, is there a way to calculate this?
07/15/20 4:54 PM 4079481564 Answered Linkcall 0 30 3
07/15/20 4:54 PM 6782966366 Answered Linkcall 167 607 414
07/15/20 4:56 PM 3343321518 Answered 10
07/15/20 4:57 PM 7706826006 Answered 13
07/15/20 4:58 PM 4079481564 Answered Linkcall 27 63 10
07/15/20 5:00 PM 4079481564 Answered 4
07/15/20 5:00 PM 4079481564 Answered Linkcall Abandoned 0 51 253
07/15/20 5:08 PM 5703506700 Answered 7
07/15/20 5:18 PM 5612552726 Answered VoiceMail 49
07/15/20 5:22 PM 3057371719 Answered 19
07/15/20 5:39 PM 4073740179 Answered VoiceMail 32
07/15/20 5:40 PM 4073740179 Answered Linkcall Abandoned 43
07/15/20 10:14 PM 3529781734 Answered VoiceMail 33
Best Answer

You are going to want to insert a "helper" column and use a formula such as the below to pull the time into a usable format (under the assumption that "07/15/20 4:54 PM" is all in the same cell of a system generated column).
=VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row)  (FIND(" ", [System Generated Column]@row) + 1))) + IF(FIND("P", [System Generated Column]@row) > 0, IF(VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row)  (FIND(" ", [System Generated Column]@row) + 1))) <> 12, 12), IF(VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row)  (FIND(" ", [System Generated Column]@row) + 1))) = 12, 12))
This will pull the hour and convert it into a 24 hour format.
Then you can use a COUNTIFS on this helper column to count how many are greater than or equal to 17 (5pm).
=COUNTIFS([Helper Column]:[Helper Column], @cell >= 17)
I am going to do some testing to see if we can do it without the helper column and will revisit if I find something.
Answers

You are going to want to insert a "helper" column and use a formula such as the below to pull the time into a usable format (under the assumption that "07/15/20 4:54 PM" is all in the same cell of a system generated column).
=VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row)  (FIND(" ", [System Generated Column]@row) + 1))) + IF(FIND("P", [System Generated Column]@row) > 0, IF(VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row)  (FIND(" ", [System Generated Column]@row) + 1))) <> 12, 12), IF(VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row)  (FIND(" ", [System Generated Column]@row) + 1))) = 12, 12))
This will pull the hour and convert it into a 24 hour format.
Then you can use a COUNTIFS on this helper column to count how many are greater than or equal to 17 (5pm).
=COUNTIFS([Helper Column]:[Helper Column], @cell >= 17)
I am going to do some testing to see if we can do it without the helper column and will revisit if I find something.

Thank you Paul! There are two columns, 1st column has the date, the 2nd has the time in this format (4:08 PM). How does that change the formula above?
Thanks again,
Brad!

I couldn't quite make it work without the helper column unfortunately.

I got #UPPARSEABLE error. On the sheet it's the 2nd column with a Column heading of "Inbound Call Time" the cell data is "5:08 PM".
I used the following:
=VALUE(MID([Inbound Call Time]@row, FIND(" ", [Inbound Call Time]@row) + 1, FIND(":", [Inbound Call Time]@row)  (FIND(" ", [Inbound Call Time]@row) + 1))) + IF(FIND("P", [[Inbound Call Time]@row) > 0, IF(VALUE(MID([Inbound Call Time]@row, FIND(" ", [Inbound Call Time]@row) + 1, FIND(":", [Inbound Call Time]@row)  (FIND(" ", [Inbound Call Time]@row) + 1))) <> 12, 12), IF(VALUE(MID([Inbound Call Time]@row, FIND(" ", [Inbound Call Time]@row) + 1, FIND(":", [Inbound Call Time]@row)  (FIND(" ", [Inbound Call Time]@row) + 1))) = 12, 12))

It changes the formula in the helper column like so...
=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row)  1)) + IF(FIND("P", [Time Column]@row) > 0, IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row)  1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row)  1)) = 12, 12))

Worked perfectly, THANK YOU!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!