Average Based on 2 Separate Criteria

kss5229
kss5229 ✭✭
edited 10/01/24 in Formulas and Functions

I'm trying to create a formula that will average the age of an event if it meets two separate criteria:
1. The event occurred in that month (date of notification)
2. The event status is "Complete"

The age of each event is already calculated for each event in a separate column. I'm getting a syntax error with the formula below:

=IFERROR(AVERAGEIF({Date of Notification}, AND(({Date of Notification}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row))), {Status} = "Complete"), {Age}), "").

I have a similar formula that does work where it's calculating the age for all events that occurred that month:

=IFERROR(AVERAGEIF({Date of Notification}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row)), {Age}), "")

Tags:

Best Answer

  • kss5229
    kss5229 ✭✭
    edited 10/01/24 Answer ✓

    Thanks, with some tweaks to the formula you provided, I was able to get it to work!

    =IFERROR(AVG(COLLECT({Age}, {Date of Notification}, IFERROR(MONTH(@cell ), 0) = MONTH([Start Date]@row), {Date of Notification}, IFERROR(YEAR(@cell ), 0) = YEAR([Start Date]@row), {Status}, "Complete")), "")

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 06/19/24

    It's much cleaner to use COLLECT to get the results you want, then average them, since there's no AVERAGEIFS function.

    Below, COLLECT is creating a temporary array of the Age values for any row that meets the criteria:

    • Date of Notification month is the same as Start Date month
    • Date of Notification year is the same as Start Date year
    • Status is Complete

    AVERAGE then averages the collection of Age values that met those criteria.

    And you already have the IFERROR to blank out if nothing is found

    = IFERROR( AVERAGE( COLLECT( {Age}, {Date of Notification}, MONTH(@cell)=MONTH([Start Date]@row, {Date of Notification}, YEAR(@cell)=YEAR([Start Date]@row, {Status}, "Complete")), "")

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • kss5229
    kss5229 ✭✭
    edited 10/01/24 Answer ✓

    Thanks, with some tweaks to the formula you provided, I was able to get it to work!

    =IFERROR(AVG(COLLECT({Age}, {Date of Notification}, IFERROR(MONTH(@cell ), 0) = MONTH([Start Date]@row), {Date of Notification}, IFERROR(YEAR(@cell ), 0) = YEAR([Start Date]@row), {Status}, "Complete")), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!