Calculate formulas by Columns

JGehrke

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

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!

JGehrke

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.