Calculate formulas by Columns

edited 08/10/22 in Formulas and Functions

I am relatively new to Smartsheet and am stumped on a formula, and so are my coworkers. I do QA for multiple teams and I need to be able to calculate information such as # of Exceptional scores (100%), Meets Expectations (85% - 99%), and Needs Improvement (0 - 84%). We tried Countifs, Averageif, and a few others and did not get results. I can't remember which formula we used for a row but it generated a 0 in the column. When inputting data as a test, it remained at 0. If I can't get this formula figured out, I will need to manually count each criterion across the board per team monthly which will really cut into my evaluation process considering I have well over 100 agents that need to be evaluated on a monthly basis.

any help would be super appreciated!



  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @JGehrke ,

    If your tests are in columns like this:

    Then you can add three additional columns to have your requirements in:

    For "Exceptional":

    =COUNTIF([Test 1]@row:[Test 6]@row, 100)

    For "Meets Expectations":

    =COUNTIFS([Test 1]@row:[Test 6]@row, <100, [Test 1]@row:[Test 6]@row, >=85)

    For "Needs Improvements":

    =COUNTIF([Test 1]@row:[Test 6]@row, <85)

    Using the example data set above, you would get these results:

    Hopefully this helps!

  • Thanks for the help. The way I need to calculate these numbers is by individual columns. So I need to know the monthly count for each of the criteria that I listed for each month, so at the end of the month I have that data to present to leadership. If the formula doesn't work, I am manually counting for each team which is 10, per month and well over 100 agents.

    Thanks again for any help, and sorry for not clarifying the the first post.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!