Averageif function looking at a list criteria

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.



Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this...


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

    thinkspi.com

  • 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

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