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}), "")

Find more posts tagged with

Formulas

Accepted answers

kss5229

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")), "")

All comments

Brian_Richardson

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")), "")

kss5229

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.