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.

    Formula to compare 2 cells and generate the difference on a 3rd cell

    ✭✭
    in Formulas and Functions

    Hello all, I am working on a trainings tracker for our project team, and I am a bit stuck. I need to generate a formula that will compare the trainings that are listed in the Required and Completed column cells, and generate the difference on the Outstanding column. Any suggestions would be greatly appreciated!

    Smartsheet Snip24Jan2024.png


    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭
      edited 01/25/24

      You may need to add some hidden cells with helper formulas listing each required and if it matched the completed

      For example have a column for Required Training A with fomula =if(Contains("Required Training A", [Completed Training]@row, "Complete", "Training Name" and so on for each required training

      Then in your outstanding column do =Join and combine all seperate Required Training columns that are not Complete

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      edited 01/25/24

      Hi @Braudy ,

      A quick solution would be to use a helper column and then sum the total of outstanding trainings in "Outstanding trainings".

      "Completed?" field column formula

      =IF(Required@row = "", "", IF(FIND(Required@row, JOIN(Completed:Completed, "-")) > 0, "Yes", "No"))

      "Outstanding" field cell formula

      =COUNTIF([Completed?]:[Completed?], "No")

      image.png


      I would love an elegant solution that did this without the helper column.

      Regards,

      Neil Egsgard

      · Share on FacebookShare on Twitter
    • ✭✭

      Thanks @Kristin Randall, given that I am tracking 60+ trainings, would I need to create a separate column for each training in order for your suggestion work?

      Thanks for your response @Neil Egsgard, is there a way to list all trainings in one cell? I am hoping to find a way to simplify/consolidate as much as possible since I am tracking a lot of trainings for a large team.

      · 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