Average If Certain Criteria is Met
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
Best Answer
-
Remove the second =
You could also look into an AVG(COLLECT( which functions in much the same way as an AFGIF would.
Answers
-
I don't think that there is a conditional average function, but you could do a sumif() / countif() to do the same thing.
-
^^^ Correct. I use the SUMIF/COUNTIF combo myself.
-
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
-
=(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.
-
Awesome, thank you!
-
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
-
Remove the second =
You could also look into an AVG(COLLECT( which functions in much the same way as an AFGIF would.
-
Thank you for the quick response, that was it! I will look into the AVG(COLLECT too.
Thanks again!
-
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.
-
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
-
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?
-
Fantastic! Thank you!
-
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,
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!