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

Sherry Fox

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.

MichaelTCA

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.

MichaelTCA

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.

Sherry Fox

https://community.smartsheet.com/discussion/comment/419124#Comment_419124

I got #UNPARSABLE initially, but I changed it to (added Quotes to Yes & No, then it worked perfectly! Thanks so much!!!!

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

MichaelTCA

@Sherry Fox

Haha darn it, you got me there! Don't tell the group of champs. 😉

Sherry Fox

@MichaelTCA ,

I am one of the new Champions, so your secret is safe with me!!!!