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

Braudy

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

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

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.

Regards,

Neil Egsgard

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.