IFERROR(AVERAGEIF(REFERENCE ANOTHER SHEET

Hi all,

Looking for some help on a formula that I keep getting an error.

I am looking to populate the average % into a breakout summary sheet by referencing multiple columns from the master sheet, but only if the Master QTR column contains a specific quarter (i.e. Q1).

I was able to figure out how to populate the average using =IFERROR(AVG({Front End}), "Compliant"), but cannot figure out how to add the QTR portion in without getting #unparseable.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try somethgin like this:

=IFERROR(AVG(COLLECT({Front End}, {Quarter}, @cell = "Q1")), "Compliant")

All comments

Eric Law

Hello @Kenny T. ,

Have you tried, =IF(QTR@row = "Q1", IFERROR(AVG({Front End}), "Compliant"), "Not Q1")?

Kenny T.

Just did, I get an #invalid operation error

Paul Newcome

Try somethgin like this:

=IFERROR(AVG(COLLECT({Front End}, {Quarter}, @cell = "Q1")), "Compliant")

Kenny T.

You're amazing, thanks!

Paul Newcome

Happy to help. 👍️

Kenny T.

@Paul Newcome I have to commend you on your expertise, whenever I go to the community for help, I only look at ones where you weigh in and look straight to your comments!

I am teaching myself how to use Smartsheet, so you're formula brain is amazing and something I aspire to have haha. Any chance you might be able to help me out with this question https://community.smartsheet.com/discussion/104413/if-vlookup-index-match-collect/p1?new=1

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.