# How to edit filter to include YEAR function

Options
✭✭✭✭

So I had this formula created and its searching by a couple of different metrics to gather the average of a column but only via the supervisor name for another column. Silly me forget we only need to spit back data from 2024 as the sheet collects multiple years worth of data and my brain is really struggling to add this addition to a formula without breaking it for some reason.

I need to take the formula below and just update it enough to include also searching for responses only within the year 2024. Any help would be appreciated :)

Current formula:

=IFERROR(AVG(COLLECT({30 Day Q 1}, {30 Day Supervisor}, [supervisor]2)), 0)

• ✭✭✭✭✭✭
Options

Try this:

=IFERROR(AVG(COLLECT({30 Day Q 1}, {30 Day Supervisor}, [supervisor]2, {Date Range}, IFERROR(YEAR(@cell), 0) = 2024)), 0)

• ✭✭✭✭
Options

Hi,

You can use YEAR formula: YEAR Function | Smartsheet Learning Center

Best,

Beata

• ✭✭✭✭
Options

I already know I need the YEAR formula in there, but I'm struggling with how to get the YEAR function added in there without breaking the current formula.

• ✭✭✭✭✭✭
Options

Try this:

=IFERROR(AVG(COLLECT({30 Day Q 1}, {30 Day Supervisor}, [supervisor]2, {Date Range}, IFERROR(YEAR(@cell), 0) = 2024)), 0)

• ✭✭✭✭
Options

That worked! I wasn't wrapping the YEAR function into another IFERROR so that helped solve my issue. Thanks for the help :)

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
Options

@Paul Newcome Let me pick your brain again about a similar issue. The formula is pretty similar but instead of getting an average, I'm just trying to count a total from a column that matches the drop-down select. But when I attempt to add the YEAR function it once again gives me the incorrect argument message

Same concept, searching for the supervisor name, but also needs to match the second criteria and then be within the year (2024)

Current Formula:

=IFERROR(COUNTIFS({Supervisor}, [Name]2, {Primary Reason}, [P.Reason]@row, IFERROR(YEAR(@cell), 0) = [Year to Review]#), 0)

• ✭✭✭✭✭✭
Options

You don't have the {Date Range} included in your formula.

• ✭✭✭✭
Options

I can't believe I missed that! I could not get this formula to work for the life of me purely because I just didn't add the date range to pull from. Guess I just needed another set of eyes on it... Thanks!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!