Average If Certain Criteria is Met

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

Hospital Billing.png

Tagged:

Answers

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    ^^^ 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

  • Paul NewcomePaul 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.

  • 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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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!

  • Paul NewcomePaul 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.

  • 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 NewcomePaul 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?

  • 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,

    Capture2.PNG

  • Paul NewcomePaul 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.

  • 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!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Excellent! Happy to help! yes

  • I am having the same issue where I am getting a #Divide By Zero result when trying to use the =Avg(collect formula. I currently have

    =AVG(COLLECT({Demographics Range 1}, {Demographics Range 3}, "MG", {Demographics Range 3}, "VP"))


    I want to know the average time in position (demographics range 1) if their job class (demographics range 3) meets certain criteria. Can anyone tell me where the error might be in my formula, I can't figure it out.


Sign In or Register to comment.