AVERAGEIF function not returning expected value

Nate H

Hi Smartsheet Community!

I have a sheet with a score column where I want to average the points in the score column for a particular category (which is a multi-select field). I've tried AVERAGEIF and AVG(COLLECT formulas but I'm unable to get either of them to work. They either return "0" or are blank, respectively. What am I doing wrong?

=AVERAGEIF(Category:Category, "Gap Closure", Score:Score)

=IFERROR(AVG(COLLECT(Score:Score, Category:Category, "Gap Closure")), "")

Nick Korna

@Nate H,

Without changing the formula I have it working as a sheet summary with the score coming from an IF statement:

The only thing I can think of is if your score determining IF statement is IF(<whatever condition>,"1") etc. - if you remove the ""s and just leave the number then it should work - using this I can duplicate your 0 outcome:

Do you want to give this a try and see if this is the culprit?

Nick Korna

Hi @Nate H,

I think this formula should do what you're after:

=AVERAGEIF(Category:Category, HAS(@cell, "Gap Closure"), Score:Score)

Hope this is of some assistance. If you've any questions etc. then just post! 😊

Nate H

@Nick Korna Thanks for the response. I neglected to mention that I'm looking to use the formula in a sheet summary field. Not sure if that is making a difference. But I tried your formula there and I'm still getting a "0" return.

Nate H

So I've figured out why I'm not getting a return. My Score column is formula driven as well. I have it putting in a 1, 2, or 3, based on the selection of another single dropdown column that isn't a numeric value but a name. I guess the AVERAGEIF won't work against an IF formula.

Nick Korna

@Nate H,

Without changing the formula I have it working as a sheet summary with the score coming from an IF statement:

The only thing I can think of is if your score determining IF statement is IF(<whatever condition>,"1") etc. - if you remove the ""s and just leave the number then it should work - using this I can duplicate your 0 outcome:

Do you want to give this a try and see if this is the culprit?

Nate H

Thanks, @Nick Korna . The quotes were indeed the problem!

Nick Korna

No worries; glad you have a solution now! 😊