#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Average If Formula

edited 12/09/19

Is there such a thing as an Average If Formula in smartsheet?  so to average only cells that meet a certain criteria?

• Employee
edited 12/19/16

Hello—

We don't have an Average IF formula in Smartsheet but I've got your vote down for this on our enhancement request list for further consideration.

With being said, you can use a SUMIF formula to sum the values that meet your conditions, then divide it by a COUNTIF formula of the values that meet your conditions.

=SUMIF(...)/COUNTIF(...)

More on formulas here: https://help.smartsheet.com/articles/775363-using-formulas

• Hello,

Just checking in to see if this functionality has been created yet? It would be very useful for my purposes, as well.

Thanks!

Ally

• I too would like to use an averageIF.

• It has been about a year and a half since this was first reported/requested.  Do we have an idea of when this is going to be added?  AverageIF is a pretty common formula for PMs and would be a great asset for dashboard reporting.  Is it possible to get this prioritized in a near term release?

• ✭✭✭✭✭✭

You can use =AVG(COLLECT(range to sum, criteria range, criterion)

The intent, I believe, of COLLECT, is to avoid have special use case functions when one tool can satisfy multiple ones.

Craig

• If the AverageIF formula still being worked on?  This would be a great feature to have.

• ✭✭✭✭✭✭

See my post below on AVG(COLLECT()).

My experience is that COLLECT()'s flexibility allows functions like AVERAGEIF to be created. This is NOT a work-around, but a design choice and a good one.

My belief is that you will never see AVERAGEIF() as a stand-alone function.

The only use case I can see is porting of existing Excel / OpenOffice spreadsheets into Smartsheet, and that is an import problem, not a function problem.

Craig

• Yep - this function would still be useful.

(The problem with the Collect function is that it does not automatically adjust when columns are added).

• ✭✭

When taking an average of data, and trying to create dashboards, you need this function to create the dashboard views at a filtered level.

If not, pivot tables to filter would truly be easier to build dashboards.

• Craig,

Do have a way to use this formula across sheets when you're trying to get the average of a child rows based on certain criteria? I got an #UNSUPPORTED CROSS-SHEET FORMULA error I think because children function is not supported.

Right now, I just added a helper column and linked the cell in the other sheet to the help column cell. However, I'd like to avoid that if possible.

Thanks,

Brett

• ✭✭✭✭✭✭

Brett,

On the source sheet, you will need to have some way to determine the rows that are CHILDREN.

That additional range and criterion to the COLLECT's list of range/criteria on the destination sheet.

It is one more step and may clutter up the source sheet, may be additional admin burden when multiple source sheets are involved, but since COLLECT does not accept the hierarchy functions, there isn't really a viable alternative.

Craig

• Employee

Hello everyone,

AverageIf is in the works, along with some other new functions. Please note that the timeline we are targeting for release (July) is subject to change and is not guaranteed.

Thank you,

Kara

• ✭✭✭✭✭✭

Excellent news Kara!

Can't wait!

Have a fantastic weekend!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.