Average Based on 2 Separate Criteria
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}), "")
Answers
-
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")), "")
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives