AVGIF or AVG(Collect)



I'm trying to avg each program based on the dept. I've used both If and Collect resulting in an error. I'm not sure if it's because I'm referencing another sheet incorrectly (the table below). Any help would be appreciated.

Thank you

have a table with a set of value streams, then columns with


  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭

    Good Morning

    Are you familiar with =AVERAGEIF()

    I would use this formula

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Melanie Bryant

    I'd be happy to help. What formula have you tried, and what error are you seeing? Also, you mentioned you are referencing another sheet. To verify I'm understanding correctly, your source sheet would look like the top half of the screenshot, and your destination sheet looks like the bottom portion? I'm somewhat confused by your statement and the screenshot, and whether your data is cross sheet referenced or if the destination cells and the source cells exist all a single sheet.

    I will assume your Smartsheet source sheet looks like the top portion and is a different sheet. Because errors sometimes occur if non-numeric values occur in the data range, we will add an additional criteria to filter for numbers only. However, doing this means we are using more than one criteria . We will use AVG/Collect which can handle multiple criteria whereas AverageIF is limited to using only one criteria.

    This is the formula that goes in the Destination sheet Program 1 column.

    =AVG(COLLECT({Source Sheet Program 1},{Source Sheet Program 1},ISNUMBER(@cell), {Source Sheet Area Audited}, [Area Audited]@row))

    Remember if this is a cross sheet formula, you cannot just copy and paste but you must create the cross sheet references using the blue link in the formula window. The {Source Sheet Program 1} range will be the entire Program 1 column in the source sheet. You will then need to create the same formula but substitute for Program 2, Program 3 etc, for each of the other columns. The only thing changing is the Program range

    Does this formula work for you? Don't hesitate to ask any question


  • Melanie Bryant

    Hi @Kelly Moore

    Thank you for the assistance. You are correct in your assumptions.

    I never hit post comment. I used the pro desk. I was trying to look at the whole table instead of the column and it fixed my issue. I got additional training on both.

    Thanks for your response!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!