Average of values if criteria is met in two other columns

Hello, I am trying to determine the average of values in one column (Days Open) if criteria in two other columns is met:

(Date Closed) is from last month
(Facility Name) is a specific dropdown option from this column

I can calculate average based upon date closed being from last month, but adding in the second criteria that the facility name from that row is specific text is giving me issue.

=AVERAGEIF(COLLECT({Days Open}, {Date Closed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()) - 1, {Facility}, FIND("NAME", @cell)))

Find more posts tagged with

Accepted answers

Kelly Moore

Hey @shoey24

Change your AVERAGEIF to AVG. The COLLECT function is filtering the same as the IVERAGEIF function would

=AVG(COLLECT({Days Open}, {Date Closed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()) - 1, {Facility}, FIND("NAME", @cell)))

Will this work for you?
Kelly

All comments

Kelly Moore

Hey @shoey24

Change your AVERAGEIF to AVG. The COLLECT function is filtering the same as the IVERAGEIF function would

=AVG(COLLECT({Days Open}, {Date Closed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()) - 1, {Facility}, FIND("NAME", @cell)))

Will this work for you?
Kelly

shoey24

https://community.smartsheet.com/discussion/comment/464946#Comment_464946

I am now getting an Invalid Data Type Error. I looked at the column for days open and it is all numbers that are calculated using a formula on the sheet. Date Closed only contains sates, and Facility is a list of 1-3 different facilities. I cannot find where the invalid data is coming from.

shoey24

Immediately as I typed that out, I realized the problem was needing the end to be @cell) >0. It is working as intended now, thank you!

Kelly Moore

@shoey24

Good catch and glad it worked. Happy Holidays

Kelly

Sign in to join the conversation!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.