COUNTIF reference not working
I have a time entry on one sheet where times are entered in the HH:MM (24 hr) format and another column that pulls the hour of that day.
[Time Received (FORM)] - time in HH:MM format
[Received Hour of Day] - pulls the hour of the day with the following formula:
=IFERROR(LEFT([Time Received (FORM)]@row, FIND(":", [Time Received (FORM)]@row + ":") - 1), "")
I created a metric sheet to show the counts for each hour of the day but it is not working. The formula there is simple:
=COUNTIF({NCQA Voicemail Tracking Range 1}, "00"))
*The "00" changes for each hour of the day.
I am getting "0" for the counts on each hour of the day and I can't figure out why.
thoughts?
Answers
-
Can you show the formula in the sheet?
-
here is the formula for Column3: Frequency Row.
I tried using the HOUR OF THE DAY row as a relative reference instead of "01" but changed because it wasn't working.
I also tried adding VALUE() because it wasn't working without.
Best example is the 06 HOUR OF THE DAY. As you can see if prior post, there are 2 entries during the 06 hour. The COUNTIF() formula calculates 0 entries.
-
Try this in the [Received Hour of Day] column:
=LEFT([Time Received (FORM)]@row, 2)
Then remove the VALUE function from the COUNTIF.
Also double check the range for your {Cross Sheet Reference} to ensure it definitely is the [Received Hour of Day] column.
-
I had the formula as it was because some entries were not using the HH:MM format (example: they would put 6:30 instead of 06:30) but I don't know that it really helped.
Either way, I changed back to your suggest formula and double checked the reference… no change. Still getting "0" as my count
-
Ah. Ok. That makes sense then. if some entries were h:mm.
What happens if you apply a filter to the source sheet to match the range/criteria set?
-
I have manually ensured all times are in the HH:MM format.
Can you elaborate on applying the filter?
-
A filter applied to the source sheet that matches your range / criteria set in your COUNTIF. So a filter on the source sheet applied to the column(s) referenced in your {Range} and the criteria for the filter would be the same as the criteria in your formula which is "equals" and then "00" or "06" or whatever other hour you are searching for.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 461 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!