How to edit filter to include YEAR function

Ashley Klaers

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)

Paul Newcome

Try this:

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

SmartWay360

Hi,

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

Best,

Beata

Ashley Klaers

@SmartWay360

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.

Paul Newcome

Try this:

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

Ashley Klaers

@Paul Newcome

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

Paul Newcome

Happy to help. 👍️

Ashley Klaers

@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)

Let me know your thoughts!

Paul Newcome

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

Ashley Klaers

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!

Paul Newcome

Happy to help. 👍️