Formulas not working after adding year?

Ashley Klaers

I'm getting one of our dashboards updated for the new year but the previous year didn't have the year part of the formula. I've gotten it added for most but I've run into a couple where when I add the year piece I can't seem to get it to work:

=COUNTIFS({Relevant #}, 1, {Submission Date}, IFERROR(YEAR(@cell ) = 2025))

=AVG(COLLECT({Sat Helper}, {Training}, FIND("Supervisor Training - Q2", @cell) > 0, {Submission Date}, IFERROR(AND(YEAR(@cell ), 0) = 2025)))

Any insights on either of these would be great as I've been staring at them throughout the day. Even the AI tools have given me similar formulas and none of them have worked.

KPH

It doesn't look like you have enough parts in your IFERROR. There is nothing to return if there is an error. Try this

=COUNTIFS({Relevant #}, 1, {Submission Date}, IFERROR(YEAR(@cell ) = 2025, ""))

Ashley Klaers

@KPH

That did help, such a simple thing!

Would you have any ides on the 2nd formula?

=AVG(COLLECT({Sat Helper}, {Training}, FIND("Supervisor Training - Q2", @cell) > 0, {Submission Date}, IFERROR(AND(YEAR(@cell ), 0) = 2025)))

Paul Newcome

You do not need the AND function or the accompanying set of parenthesis.

KPH

Hi @Ashley Klaers

You can make the same change to the AVG COLLECT as you made to the COUNTIFS

Remove the part in bold:

=AVG(COLLECT({Sat Helper}, {Training}, FIND("Supervisor Training - Q2", @cell) > 0, {Submission Date}, IFERROR(AND(YEAR(@cell ), 0) = 2025)))

Replace it with this part in bold:

=AVG(COLLECT({Sat Helper}, {Training}, FIND("Supervisor Training - Q2", @cell) > 0, {Submission Date}, IFERROR(YEAR(@cell ) = 2025, "")))