Looking for assistance with a formula

Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function.

I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list.

I have the following columns I am trying to avg: [County], [Service], [Openings]

I am looking for the avg openings specific to a county and service.

Any help would be greatly appreciated!

Thanks in advance!

Find more posts tagged with

Sheets

Formulas

Reports

Accepted answers

All comments

Mike Jennings

The way that comes to mind when trying to find an average based on multiple criteria is using the function "collect" in addition to your average function.

=avg(collect({openings column}, {County Column}, "County Criteria", {service column}, "Service Criteria"))

Ella_80

Thank you, I tried this and unfortunately it did not work. :(

nicksnav

This formula should work. If you're displaying the average on the same sheet as your criteria the formula would be.

=AVG(COLLECT([Openings]:[Openings], [County]:[County], "County Criteria", [Service]:[Service], "Service Criteria"))

Try that. If it doesn't work can you display your County and Service criteria?

Ella_80

I can't display my data unfortunately. But I did try this and got a different error message than before "#divide by zero"

Any thoughts?

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.