Formula for first/last value based on year.


I have a large sheet where employees completed inventory checks every week over the last few years. Trying to figure out a formula to get the first and last entry based on a cell that contains last years "year". This is to gather our yearly starting inventory and yearly ending inventory.

This is the formula I have to get the last quantity entered on the sheet but I cannot figure out how to incorporate the cell with last year as a criteria.

=IFERROR(INDEX({Item 1}, MATCH(MAX(COLLECT({DATE}, {Item 1}, @cell <> "")), {DATE}, 0)), "")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!