Average of values if criteria is met in two other columns

shoey24 · 12/09/24

Hello, I am trying to determine the average of values in one column (Days Open) if criteria in two other columns is met:

(Date Closed) is from last month
(Facility Name) is a specific dropdown option from this column

I can calculate average based upon date closed being from last month, but adding in the second criteria that the facility name from that row is specific text is giving me issue.

=AVERAGEIF(COLLECT({Days Open}, {Date Closed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()) - 1, {Facility}, FIND("NAME", @cell)))

Kelly Moore · 12/10/24

Hey @shoey24

Change your AVERAGEIF to AVG. The COLLECT function is filtering the same as the IVERAGEIF function would

=AVG(COLLECT({Days Open}, {Date Closed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()) - 1, {Facility}, FIND("NAME", @cell)))

Will this work for you?
Kelly

Kelly Moore · 12/10/24

Hey @shoey24

Change your AVERAGEIF to AVG. The COLLECT function is filtering the same as the IVERAGEIF function would

=AVG(COLLECT({Days Open}, {Date Closed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()) - 1, {Facility}, FIND("NAME", @cell)))

Will this work for you?
Kelly

shoey24 · 12/10/24

https://community.smartsheet.com/discussion/comment/464946#Comment_464946

I am now getting an Invalid Data Type Error. I looked at the column for days open and it is all numbers that are calculated using a formula on the sheet. Date Closed only contains sates, and Facility is a list of 1-3 different facilities. I cannot find where the invalid data is coming from.

shoey24 · 12/10/24

Immediately as I typed that out, I realized the problem was needing the end to be @cell) >0. It is working as intended now, thank you!

Kelly Moore · 12/10/24

@shoey24

Good catch and glad it worked. Happy Holidays

Kelly

Average of values if criteria is met in two other columns

Best Answer

Answers

Help Article Resources

Categories