Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Using COUNTIF(S) with RIGHT functionality

    ✭✭✭
    in Formulas and Functions

    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?

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    Answers

    • Community Champion
      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! 🙂

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Thank you, @Nick Korna! This works perfectly!

      · Share on FacebookShare on Twitter
    • Community Champion

      No problem, happy to help! 😊

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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")), "")

      · Share on FacebookShare on Twitter
    • Community Champion

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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?

      · Share on FacebookShare on Twitter
    • Community Champion

      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.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions