# 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:56 PM 3343321518 Answered 10

07/15/20 4:57 PM 7706826006 Answered 13

07/15/20 5:00 PM 4079481564 Answered 4

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 10:14 PM 3529781734 Answered VoiceMail 33

• 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,

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!