Averageif function looking at a list criteria

Pawillia

I am trying to create an averagif formula that looks at a list of values in a cell. Currently if I do it the normal way it just returns back if this specific value is the only thing in the cell. But once there are multiple items in the cell it doesn't count that within its result.

Both formulas return the same way per what I've seen on various threads.

=AVERAGEIF({Resource Heatmap Range 1}, "AMRS", {Resource Heatmap Range 2})

=AVG(COLLECT({Resource Heatmap Range 2}, {Resource Heatmap Range 1}, @cell = "AMRS"))

Hopefully there is a way to average values based on a criteria that sits within a list on smartsheet.

Paul Newcome

Try this...

=AVG(COLLECT({Resource Heatmap Range 2}, {Resource Heatmap Range 1}, HAS(@cell, "AMRS")))

Pawillia

Hey Paul - Thank you for the response. I tried the above and now get UNPARSEABLE as an error.

The data set can be simple with the below if you needed an example. Per the dataset below my original formula gave me 3. MY expectation would be to see that there are 3 places with AMRS in it so I would want the average of all 3 columns which would be 4.

Row 1

John

Jacob

James

Row 2

5

4

3

Row 3 (List)

AMRS, EMEA, APAC

AMRS, EMEA

AMRS

Pawillia

Never mind. I figured out what was happening. Thank you!