Averageifs

Jermain Johnson

Smartsheet does not appear to contain the averageifs formula. Is there an alternative that will perform a similar function. I would like to collect an average score of a sales person based on a text field which contains the region serviced. As there may be overlap in some instances I can not use the averageif option

For example: If one Sales person covers both North and East I would like to avg the scores of both of those regions for that individual.

Kelly Moore

oh wait, maybe I misunderstood. Do you still want the salespeople, but you're not referencing them by a list? If that is the case, you'll have to write in their names individually, one at a time.

Using the first formula

=AVG(COLLECT([Score Field]:[Score Field], [Contact Field]:[Contact Field], @cell = "Jane Doe", [Region]:[Region], OR(@cell="North", @cell="East")))

Jermain Johnson

Thank you for your assistance this was very helpful.

Kelly Moore

Hey Jermain

The combination of Functions AVG(COLLECT) works

Assuming the information is all in the same sheet, it could look something like this:

=AVG(COLLECT([Score Field]:[Score Field], [Contact Field]:[Contact Field], contact@row, [Region]:[Region], OR(@cell="North", @cell="East")))

Be sure to change the column names to match your column names.

Jermain Johnson

Hello I'd like to make an adjustment. The sales persons name will not be in the sheet. Only the region and the score field. How would I adjust the formula accordingly?

Kelly Moore

Hey,

The COLLECT formula has the syntax of (Range of desired information, range of criteria 1, criteria 1, range criteria2, criteria 2, etc). If you want to add or delete information, you need to edit both the range and the criteria.

You will want to delete the [Contact Field]:[Contact] range and it's criteria, contact@row . When you do this, make sure you're not leaving any extra spaces in the formula. If you end up with an Unparseable, that's one of the first thing to look for.

=AVG(COLLECT([Score Field]:[Score Field], [Region]:[Region], OR(@cell="North", @cell="East")))

Does this make sense?

Kelly Moore

oh wait, maybe I misunderstood. Do you still want the salespeople, but you're not referencing them by a list? If that is the case, you'll have to write in their names individually, one at a time.

Using the first formula

=AVG(COLLECT([Score Field]:[Score Field], [Contact Field]:[Contact Field], @cell = "Jane Doe", [Region]:[Region], OR(@cell="North", @cell="East")))

Jermain Johnson

Thank you for your assistance this was very helpful.