Using COUNTIF(S) with RIGHT functionality


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?

Best Answer


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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! 🙂

  • Thank you, @Nick Korna! This works perfectly!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, happy to help! 😊

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!