max date formula combined with a condition?

Any idea how I could write a formula that gives me the max date for rows, where the the checkbox is checked?

I know to use the MAX formula to find the max date. But how can I restrict the search to only the rows, that have a checkbox checked?

Thanks for giving me some ideas.

Best Answer


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    @Barbara Witt

    Try this

    =MAX(COLLECT([your date]:[your date], [your checkbox]:[your checkbox], 1))

    Remember this formula will have to reside in a date column since you are collecting a date.


  • Beautiful, KDM, that worked.

    =MAX(COLLECT({DAX iOS Inventory Range 1}, {DAX Engagement

    Tracking Range 8}, 1))

    Now, I need to include the MAX formula with my existing IFERROR formula. I am using it to insure data is only displayed, if there is any. If there is no date to display, then it should leave the field blank. Can I combine the IFERROR fomula with the MAX formula to 1. only display a date if one was found and 2. display the greatest date that was found.

    I have tried the following, but it will not pick up the most recent date.

    =MAX(IFERROR(VLOOKUP([Project Number]@row, {DAX iOS Inventory Range 5}, 3), " "))

    =(IFERROR(MAX(VLOOKUP([Project Number]@row, {DAX iOS Inventory Range 5}, 3), " "))

    =(IFERROR(VLOOKUP([Project Number]@row, MAX( {DAX iOS Inventory Range 5}, 3), " "))

    Thanks so much for your continued support.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Barbara Witt

    Happy to help, I just need a bit of clarification.

    When you say you want the IFERROR with your existing formula - do you mean the MAX(Collect) formula we just worked on, or an additional formula? If an additional formula, is that formula working without the IFERROR?

    For the formula above, the IFERROR should be

    IFERROR(MAX(COLLECT({DAX iOS Inventory Range 1}, {DAX Engagement

    Tracking Range 8}, 1)),"")

    If you are looking to execute the above IF only when another datefield is not empty then put an IF before the MAX(Collect)

    IF(ISDATE([your date field]@row, MAX(COLLECT({DAX iOS Inventory Range 1}, {DAX Engagement

    Tracking Range 8}, 1)))

    Let me know and we can continue to build your formulas


  • Barbara Witt
    Barbara Witt ✭✭
    edited 02/10/21

    Sorry for the confusion, Kelly.

    It appears your first assumption is right, but it will not give me the max date.

    This is my original formula: IFERROR(VLOOKUP([Project Number]@row, {DAX iOS Inventory Range 5}, 3), " ")) I am matching up the project number and then it gives me the value in column 3, which is a date. If no date, then it keeps the field empty.

    I could have several rows with the same project number and I only want the most recent date out of all the dates, that may be associated with that specific project number at row.

    Can I use MAX in conjunction with my VLOOKUP (and the IFERROR, so it does not put a generic placeholder word if no date can be found)?

    I tried your suggestions from above, but it will not display the max date.

    IFERROR(MAX(VLOOKUP([Project Number]@row, {DAX iOS Inventory Range 5}, 3)), " ")

    Well, there must be something else wrong. If I take the MAX back out, it will give me the correct date for 2 rows (there is only one project number and one date), however I have two other rows, that have only one date and one project number, and they are not showing up......

    Oh no. Any suggestions? Thanks, Barbara

  • Christy Kooiman
    Christy Kooiman ✭✭✭✭

    I'm having the same issue except trying to find the minimum not max. I did your recommendation above and am getting # Invalid Column Value?

    =MIN(COLLECT([End Date]:[End Date], [Team Member]:[Team Member], "Employee Name"))

    What am I doing wrong? Any help would be greatly appreciated!!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Christy Kooiman

    Is the column formatted as a date column? Also, are you looking for the word “Employee Name”, or are you looking for [Employee Name]@row (no quote marks)?

  • Christy Kooiman
    Christy Kooiman ✭✭✭✭
    edited 05/07/21

    Hi @KDM it's a v-lookup from a different sheet!!! I didn't even think of that! However I tried in the other sheet and it's still not working. The date column is set to a date column

    =MIN(COLLECT(Finish:Finish, PM:PM, "Employee Name"))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Let me know if you need any help or if you want to convert this to a cross sheet referenced formula (if you have the linkages)

  • Christy Kooiman
    Christy Kooiman ✭✭✭✭
    edited 05/07/21

    I'm now getting #Invalid Column Value in the new sheet. This sheet does have the date column as a true date column...

    Employee Name is a lookup, not a column. The column is PM, and then I want to search on just one employee.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭


    Two possible error causes to explore

    1- In the date data you are wanting to collect - are there errors or non-date values in this range? How is that date originating?

    2- whatever criteria that you are using for the formula you are testing - is there an 'response' it should actually pull from the data set, ie - if you changed the employee name or some other criteria, do you also receive an error?

    If it is possible to give me a screenshot of the data and your formula, that will help me (and the community) troubleshoot.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!