# Average If Certain Criteria is Met

Options
✭✭✭✭
edited 12/09/19

Hello,

I'm trying to calculate the average number of days but only for columns that meet the criteria.  I have a formula that is calculating the networkdays between two dates. This column is called Implementation Time (Days) - BD. I am now trying to average those days but only for when the Work Type column = Hospital Billing.  Does anyone have any advice?

I included a screen shot.

Thank you,

Sean

Tags:

«1

• Options

I don't think that there is a conditional average function, but you could do a sumif() / countif() to do the same thing.

• ✭✭✭✭✭✭
Options

^^^ Correct. I use the SUMIF/COUNTIF combo myself.

• ✭✭✭✭
Options

I was going to try that.  I saw that in other threads as a way to get it.  How would the formula look though to include both columns?  sumif(worktype, "Hospital Billing", implementation time(days)-BD)/countif("Hospital Billing", implementation time(days)-BD))

Thank you,

Sean

• ✭✭✭✭✭✭
Options

=(SUMIF([Work Type]:[Work Type], "Hospital Billing", [Implementation Lag Time - BD]:[Implementation Lag Time - BD]) + SUMIF([Work Type]:[Work Type], "Hospital Billing", [Implementation Time - BD]:[Implementation Time - BD])) / COUNTIF([Work Type]:[Work Type], "Hospital Billing")

This adds your rows for the 2 Time columns together if the Work Type column is "Hospital Billing" then divide the total by the count of rows with "Hospital Billing" in the Work Type column.

• ✭✭✭✭
Options

Awesome, thank you!

• ✭✭✭✭
Options

I'm also looking for the averageif function and read a few postings.  I keep trying to use the sumif/countif formula but I'm getting #invalid operation error?

=SUMIF(Recruiter1:Recruiter93, "Erika Golla", [Days from posted to accepted]1:[Days from posted to accepted]93) / =COUNTIF(Recruiter1:Recruiter93, "Erika Golla")

Without seeing my sheet can anyone see where my error is in the above formula?

TIA

• ✭✭✭✭
Options

Thank you for the quick response, that was it!  I will look into the AVG(COLLECT too.

Thanks again!

• ✭✭✭✭✭✭
Options

Happy to help. Basically you would use the COLLECT function to collect the range you want to average based on the criteria you set. The COLLECT essentially replaces the IF.

• Employee
Options

Hi Michelle,

This was just released:

=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

This is awesome! Would there also happen to be an AVERAGEIFS, or will we still need to use the COLLECT function if there are multiple sets of criteria we want to hit against?

• ✭✭✭✭
Options

Fantastic! Thank you!

• Options

Hi all - any help would be greatly appreciated!!  I recreated the above formula to fit my scenario

=(SUMIF([Implementation Type]:[Implementation Type], "Standard", [# of Day From Agreement to Project Start]:[# of Day From Agreement to Project Start]) + SUMIF([Implementation Type]:[Implementation Type], "Standard", [# of Day From Agreement to Project Start]:[# of Day From Agreement to Project Start])) / COUNTIF([Implementation Type]:[Implementation Type], "Standard")

It returns a #Divide by Zero error message.  I can't figure out how to correct this issue.

What am I missing?

Thanks,

• ✭✭✭✭✭✭
Options

A couple of things...

In your provided screenshot, there are no cells within the Implementation Type column that read as just "Standard". Since there are no cells that are just "Standard", your COUNTIF statement is returning a zero. That zero is what is causing your error message.

What exactly are you trying to average based on what criteria? There are a number of possible solutions based on the details.

• Options

Paul - thank you for your quick reply.  I realized after you said there were no "Standard" in my list that it was "Standard ACP" and now works!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!