AVERAGEIFS or AVG(COLLECT)

Fairly new to Smartsheet but well versed in Excel. There has been a learning curve, but I have overcome it until now.

I need to write a formula that will average the "Days Off" Column if the "Actual Delivery Date" Column is within a certain date range.

For this example, we can use a date range in the "Actual Delivery Date" column of 1/3/22 - 1/7/22.

Any help the community can provide would be Greatly Appreciated!

Find more posts tagged with

Accepted answers

All comments

James Keuning

Create a helper column called helper:

=AND([Actually Delivery Date]@row > DATE(2022, 1, 3), [Actually Delivery Date]@row < DATE(2022, 1, 7))

Then use:

=AVG(COLLECT([Days Off]:[Days Off], Helper:Helper, true))

Mike Sikes

https://community.smartsheet.com/discussion/comment/323061#Comment_323061

This actually returned an @row error. I tried identifying row types to make sure that everything matched and everything looks good.

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