Calculating an Average based upon a value in other column

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?

Find more posts tagged with

Hospitality

Formulas

Project Management

Comments

L_123

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)

Paul Newcome

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"))

Kandee Evans

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.

I already had it calculating the total number of closed implementations.

=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

Paul Newcome

Glad you were able to find a working solution!

Kara Lumley

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

L_123

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

Andrée Starå

Hi,

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

There are a lot of new functions.

Heres more info about AVERAGEIF: https://help.smartsheet.com/function/averageif

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…