IF Formula with Multiple Criteria to Validate

Samantha S.

Hello. I am working on an IF formula and need some help. We have employees take a test. There are 11 sections, each with a Pass/Fail grade. If they fail any section besides "SRT/PTA agree" the test is an overall "Fail". I have the formula written out to determine the overall grade for an attempt.

This formula works as designed, but now we have altered the grading system. Each employee has three attempts to take an pass this test and now only need to pass each section in at least one of these attempt. Meaning if they fail a section in two different attempts, but pass it in another attempt, then it's an overall pass for that section.

I'm struggling to figure out a formula that essentially merges the three attempts, overriding a failed section if they passed on another attempt. Each employee has a unique UserID that I'm sure I have to use to identify their three attempts, but I'm stuck. Any thoughts? Do I need a helper column for the overall grade for each section first?

Lucas Rayala

Hi @Sam H. , to confirm -- they only need to pass each test once, but they have to pass at least once. You don't need a nested IF, you want to use an IF with an AND statement and a series of COUNTIFS. In an IF(AND(), statement, every statement within the "AND" needs to be true in order for the entire statement to be true. In your case, you want the user to pass each of these tests at least once, which is where the COUNTIFS comes into play. COUNTIFS counts the number of time your designated criteria occurs -- in this case, your criteria are the user ID and the "Pass" within the individual test column--if you see at least one instance where, for that user ID, there is a pass in the stated test column (i.e. the count of this occurrence is greater than zero), then that tests passes. Do this for all the applicable tests -- I filled in two for you.

IF(AND(COUNTIFS(UCL:UCL, "pass", UserID:UserID, UserID@row)>0, COUNTIFS(MCL:MCL, "pass", UserID:UserID, UserID@row)>0),"Pass","Fail")

Samantha S.

Thank you Lucas. This worked perfectly. One additional piece to this I could use help on though. Now that I have the tests scored appropriately, I need to add that Overall Grade to my master list of all employees. The catch here is that I need their Overall Grade pulled if they took the test, but the field to be left blank if they have not yet taken the test.

For example, the Current Status is showing correctly for Kevin, but the rest showing have not taken the test at all, so should be blank or "Not Started" instead of "Failed".

Results Sheet where grade is being pulled from:

Samantha S.

Thank you Lucas. That worked perfectly. I could use help on one additional piece to this. I need to now pull their Overall Grade receive using the formula you helped with to now generate that grade on my master sheet of all employees. The issue is that my formula is defaulting to "Failed" if they have not taken the test, when it should be blank or "Not Started".

Master Employee List where Current Status needs to display Overall Grade from Results sheet or be blank if no results are found because they have not yet taken the test. Kevin is showing correctly, but the rest have not taken the test.

Results sheet that Overall Grade is being pulled from:

Samantha S.

I have tried adding in the additional criteria of "<>" but keep getting #INVALID OPERATION. Any thoughts?

Paul Newcome

Try something like this:

=IF(COUNTIFS({UserID}, @cell = UserID@row)> 0, IF(COUNTIFS({UserID}, @cell = UserID@row, {Overall Grade}, @cell = "Pass")> 0, "Pass", "Fail"))

Samantha S.

Hi Paul. I tried that, but the result is the same. Those that have not taken the test and have no UserID on the Results tracker are being flagged as a Fail instead of left blank.

Paul Newcome

Can you provide a screenshot of the formula actually in the sheet similar to the snippet below? The formula I posted should have no output if the User ID is not found on the source sheet.

Samantha S.

I was actually able to get it to work after I fixed a small error on my part, so thank you. I also am hoping for the same kind of fix for this other issue in the same sheets. For each time they attempt the test, I have this master sheet pull the results of each of their three attempts. I have this working, but if they have not attempted the test, can the field show blank instead of #Invalid Value?

Paul Newcome

Glad you got that working.

For the second bit... You need an IFERROR.

=IFERROR(original_formula, "")

Samantha S.

Oh perfect. Thank you for that. Hoping you can help me with another piece of this project. If an employee fails an attempt of the test, they are required to take one or more virtual training courses. I created this formula to pull the courses they completed after the attempt, but it is pulling with a comma instead of a multi-select list. My column to the left uses INDEX instead of JOIN, but I can't use that for this new formula or it only pulls one of the courses completed instead of them all. I can't figure out what I'm missing.

Raw data being pulled:

Paul Newcome

Replace the comma delimiter with a line break delimiter.

" , "

with

CHAR(10)

Samantha S.

Thank you so much for your help

Samantha S.

I also have this other one, where one of the criteria is that the course contains the word "Part" in it. Is there a way to add that in?

Paul Newcome

Yes. It would be another range/criteria set within the COLLECT function.

{Course Range}, CONTAINS("Part", @cell)

Samantha S.

Thanks again.