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?

Find more posts tagged with

Formulas

Countif

Accepted answers

All comments

Paul Newcome

Can you show the formula in the sheet?

BSReid24

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.

Paul Newcome

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.

BSReid24

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

Paul Newcome

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?

BSReid24

I have manually ensured all times are in the HH:MM format.

Can you elaborate on applying the filter?

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.