Trying to apply an AVERAGE IF formula

I am running the Employee Pulse Check template for a project survey for our installer team.

In a nutshell, form submitters will use a drop down to select which installer they had for the project, and then rate them on 5 questions.

I need to average out the ratings for the 5 questions, per installer.

Columns I would like to use in the AVERAGEIF formula in the "Installer Average Rating" column are those in gray.

I have a pretty substantial drop down list for calling out which installer was used:

I understand I will need a part of the formula dedicated to each installer name, but each time I try I am getting an #UNPARSABLE error and can't figure out where I am going wrong.

=AVERAGEIF(Provision Installer:Provision Installer, "Joe C.", [Converted Install - Quality]:[Converted Install - Quality]) and so on with each installer name and column to be averaged.

Thanks for your help!



  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 01/06/24

    Hello @Shelby Kriesel,

    The issue with your formula is that column names that are more than one word need brackets around them (i.e., Provision Installer needs to be written as [Provision Installer], so change it to this:

    =AVERAGEIF([Provision Installer]:[Provision Installer], "Joe C.", [Converted Install - Quality]:[Converted Install - Quality]) and so on with each installer name and column to be averaged.

    On another note - Is your end goal to get an average rating for each installer? If so, I would create a separate Metrics Sheet that lists the name of each installer and then references the Employee Pulse Check Sheet. I suspect (but am not sure, since I am not a mathematician) that you would want to sum the individual columns and then create an average off that.

    Imagine the Metrics Sheet has a column named "Installer" with the names of each installer as they are available in the Employee Pulse Check Sheet.

    I would create columns in the Metric Sheet for each question and then add

    =SUMIF({Cross Sheet Reference to Column in Employee Pulse Check}, installer@row)

    Then, you could make an Average column and average each of these columns and you have your data.

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!