COUNTIFS Formula with Multiple Criteria

Sam H.
Sam H. ✭✭✭✭
edited 04/13/23 in Formulas and Functions

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?


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)), "")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!