Average If Certain Criteria is Met

Sean Taber

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

Paul Newcome

Remove the second =

 

You could also look into an AVG(COLLECT( which functions in much the same way as an AFGIF would.

bdchapin

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

Paul Newcome

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

Sean Taber

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

Paul Newcome

=(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.

Sean Taber

Awesome, thank you!

MichelleBohn

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

Paul Newcome

Remove the second =

 

You could also look into an AVG(COLLECT( which functions in much the same way as an AFGIF would.

MichelleBohn

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

Thanks again!

Paul Newcome

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.

Kara Lumley

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

Paul Newcome

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?

MichelleBohn

Fantastic! Thank you!

hdixon100886

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,

Paul Newcome

A couple of things...

 

Both of your SUMIF statements are identical. Was this intentional?

 

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.

hdixon100886

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!