# Calculating an Average based upon a value in other column

Options
edited 12/09/19

I have a calculation to calculate the number of days between two columns.

=IFERROR(NETWORKDAYS([Date Submitted]41, [Go Live Date]41), " ")  It works great to return a blank if the customer has not gone live.

Then I have formula to calculate the average of the cells.

=AVG([Number of Days to Implement]12:[Number of Days to Implement]41)  Also works great !

I now want to calculate the average based upon a region (EAST, WEST, CENTRAL, MOUNTAIN)

I tried this formula but it is not working.

=AVG([Number of Days to Implement]12:[Number of Days to Implement]41, [US Region]:[US Region], "Central")

Any suggestions?

• ✭✭✭✭✭✭
Options

smartsheet does not currently have an avgif formula. I recommend submitting a product enhancement request in the bar to the right.

That said, you can achieve the same results by using a sumif()/Count()

=sumif([US Region]12:[US Region]41, "Central",Number of Days to Implement]12:[Number of Days to Implement]41)/Count([US Region]12:[US Region]41)

• ✭✭✭✭✭✭
Options

You could also use an AVG(COLLECT( formula which works exactly how an AVGIFS would work.

=AVG(COLLECT([Number of Days to Implement]12:[Number of Days to Implement]41, [US Region]:[US Region], "Central"))

• Options

Thank you to both for your assistance.  The average days were not calculating correctly with the formula but I was able to make a slight adjustment based upon another calculation I have in my sheet.

=COUNTIFS([US Region]:[US Region], "East", Closed:Closed, "Yes")

So I used that cell to do the division and it worked perfectly.

=SUMIF([US Region]15:[US Region]44, "East", [Number of Days to Implement]15:[Number of Days to Implement]44) / COUNT([After Hours]2)

After Hours is the column name I have for the COUNTIF formula

• ✭✭✭✭✭✭
Options

Glad you were able to find a working solution!

• Employee
Options

Hi Kandee,

This may help in the future:

=AVERAGEIF(range, criteria, [average_range])

Returns the average of cells that meet a given criteria.

Example: =AVERAGEIF([Value 1]200:[Value 2]280, >100)

Best,

Kara

• ✭✭✭✭✭✭
Options

Kara, when was this added? i double checked the formula list and even checked it in a blank sheet to make sure that averageif wasn't a function yet when i posted. I also checked the release notes and there is no mention of it

• ✭✭✭✭✭✭
Options

Hi,

It was added yesterday, or at least I noticed it yesterday.

There are a lot of new functions.

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!