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

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!


  • Kristin Randall
    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

  • Neil Egsgard
    Neil Egsgard ✭✭✭✭✭
    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")

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


    Neil Egsgard

  • Braudy
    Braudy ✭✭

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!