IFERROR(AVERAGEIF(REFERENCE ANOTHER SHEET

Kenny T.

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.

Paul Newcome

Try somethgin like this:

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

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