Using COUNTIF(S) with RIGHT functionality

Hello!

We have a record ID number that includes text. The first handful of records were entered manually for historical purposes. Now, records coming through the Smartsheet intake form are automated to begin with the number 100. For the dashboard, I'm looking to only count records with an ID greater than 100.

This doesn't seem to work: =COUNTIF(RIGHT({{Record ID}}, 3, @cell>100).

Is there a better way to filter out the records?

Find more posts tagged with

Accepted answers

Nick Korna

Hi @mónicas,

Something like this should do the trick:

=COUNTIF({Record ID 3}, RIGHT(@cell, 3) >= 100)

Hope this helps, but if you've any problems/questions then just post! 🙂

All comments

Nick Korna

Hi @mónicas,

Something like this should do the trick:

=COUNTIF({Record ID 3}, RIGHT(@cell, 3) >= 100)

Hope this helps, but if you've any problems/questions then just post! 🙂

mónicas

Thank you, @Nick Korna! This works perfectly!

Nick Korna

No problem, happy to help! 😊

mónicas

Hello again!

@Nick Korna, I'm attempting to update this metric to say, count these if the routing ID is greater than 100, if the initial review status or the updates approved column="declined"...the iferror portion is working as expected...

Can you help me sort out the correct format?

=IFERROR(COUNTIFS({Routing Request ID}, RIGHT(@cell, 3) >= 100, OR({Initial Review Status} = "Declined", {Updates Approved} = "Declined")), "")

Nick Korna

OR is used in the criteria stage, so you here you would do 2 separate COUNTIFS and add them together (or add in a 3rd one to remove the duplicates if both Initial Review Status and Updates Approved can be declined), along the lines of this:

=COUNTIFS({Record ID}, RIGHT(@cell, 3) >= 100, {Initial Review Status}, "Declined") + COUNTIFS({Record ID}, RIGHT(@cell, 3) >= 100, {Updates Approved}, "Declined") - COUNTIFS({Record ID}, RIGHT(@cell, 3) >= 100, {Initial Review Status}, "Declined", {Updates Approved}, "Declined")

If you don't have the situation where you have both Initial Review Status & Updates Approved being declined on a single row, you won't need the final COUNTIFS statement.

This is without the IFERRORs, but you should be able to enclose each of the COUNTIFS in one if required.

Hope this answers your question, but let me know if there are any questions/problems.

mónicas

I see! And if I wanted to limit that to a category within a multiple-select column, for example, {Department}, CONTAINS("Name", @cell)), I can't just add it at the end, right?

Nick Korna

You should be able to add this on to each COUNTIFS as it's just another criteria to check for - just remember you'll need it in each one.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.