Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Average of values if criteria is met in two other columns

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

  1. (Date Closed) is from last month
  2. (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)))

Best Answer

  • Community Champion
    Answer ✓

    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

Answers

  • Community Champion
    Answer ✓

    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

  • 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.

  • 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!

  • Community Champion

    @shoey24

    Good catch and glad it worked. Happy Holidays

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions