COUNTIFS Formula with Multiple Criteria

Samantha S.

I have a formula that counts the 3 test results from another sheet and says that if their Overall Grade is Mastery (Pass) then mark them as Mastery on this sheet. This functions as designed. I now need to take into account a 4th test attempt which is on the same sheet as this formula and I can't figure out how. Basically if their Overall Grade on the other sheet is "Mastery" (pass) then their would not be a 4th attempt and my current formula is correct, but if their Overall Grade on the other sheet is "Development" (fail) then they would have a 4th attempt and then the outcome of the 4th attempt needs to overwrite what the outcome of the other 3 attempt were. Any thoughts?

Paul Newcome

I would suggest setting up a helper column on the source sheet with a formula that will output either Pass or Fail (or whichever text you prefer to use) based on the data within that row.

Somethign along the lines of

=IF([Attempt #4 Outcome]@row <> "", [Attempt #4 Outcome]@row, [Overall Grade]@row)

Then I would use an INDEX/MATCH to pull this helper column based on the UserID (with an IFERROR for ones that don't have a match on the UserID).

=IFERROR(INDEX({Helper Column}, MATCH(UserID@row, {User ID Column}, 0)), "")

Paul Newcome

I would suggest setting up a helper column on the source sheet with a formula that will output either Pass or Fail (or whichever text you prefer to use) based on the data within that row.

Somethign along the lines of

=IF([Attempt #4 Outcome]@row <> "", [Attempt #4 Outcome]@row, [Overall Grade]@row)

Then I would use an INDEX/MATCH to pull this helper column based on the UserID (with an IFERROR for ones that don't have a match on the UserID).

=IFERROR(INDEX({Helper Column}, MATCH(UserID@row, {User ID Column}, 0)), "")