Potential IF/AND with multiple criteria (each listed multiple times)

Okay, let's start out by saying I am so stuck I have no clue on how to create this formula! LOL

Okay the formulas will be added to the PA - CAPA List sheet. The Training Report is the data source. This is just a small sample of my data (there are about 25K rows). In the sample on the Training Report you can see the names in the first column repeat. This is because there is a record for every person that took a training. I have highlighted 3 instances of Training - 123 we can use for our example, everything else will be very similar once I get the format of this formula. Okay on the PA - CAPA List sheet for Training - 123 I wish to list a True/False (or Yes/No) as to whether or not the Primary Approver has taken the training listed in the heading. As you can see on the Training Report that Jones, Subhaan, shows he took 3 trainings (rows 2, 3 & 4). And that row 2 is the specific training in the header, Training - 123. So Training - 123 should be listed as True/Yes. And the next name, Wu, Archie, had 7 different trainings. But none were Training - 123, so the result should be False/No.

I am having so many issues with this, as I do not want the results (like in a lookup). Plus there are names and titles are both listed multiple times. Thanks in advance.


Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Best Answer

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓

    Hey @Sherry Fox

    =IF(COUNTIFS({User Full Name}, [Primary Approver]@row, {Curriculum Title}, "Training - ABC")>0,YES,NO)

    So we will calculate how many times that person has received the training, If it's greater than 0, then they have received training, otherwise they haven't.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!