Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Return the max date looking at multiple criteria

    ✭✭✭✭
    in Formulas and Functions

    Trying to add the most recent training completion date (column is date/non-restricted) using mulitple criteria, to include: email address, training type ("refresher", or "intensive") and status ("attended") to a designated column (date/non-restricted) on another sheet.

    The formulas below are pulling the same date into each column on the metric sheet, so it appears that the formula's are not considering the training type. Idea's on how to correct?

    =MAX(COLLECT({2_All Training Attendance Training Date}, {2_All Training Attendance Email Address}, [E-mail Address]@row), {2_All Training Attendance Training Type}, "Intensive", {2_All Training Attendance Status}, "Attended")

    =MAX(COLLECT({2_All Training Attendance Training Date}, {2_All Training Attendance Email Address}, [E-mail Address]@row), {2_All Training Attendance Training Type}, "Refresher", {2_All Training Attendance Status}, "Attended")

    Tags:
    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭✭✭
      Answer ✓

      Goodness… I figured it out. I forgot to add the equals (=) sign into my formula on each criteria. Oops!

      Corrected formula is below:

      =MAX(COLLECT({2_All Training Attendance Training Date}, {2_All Training Attendance Training Type}, ="Refresher", {2_All Training Attendance Status}, ="Attended", {2_All Training Attendance Email Address}, =[E-mail Address]@row))

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭
      Answer ✓

      Goodness… I figured it out. I forgot to add the equals (=) sign into my formula on each criteria. Oops!

      Corrected formula is below:

      =MAX(COLLECT({2_All Training Attendance Training Date}, {2_All Training Attendance Training Type}, ="Refresher", {2_All Training Attendance Status}, ="Attended", {2_All Training Attendance Email Address}, =[E-mail Address]@row))

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions