Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Count, sum, average
I use Smartsheet to track employee certifications. I'd like to determine the average number of certifications per person at each site/area. I'm able to use an equation to determine the number of certifications each person has, but that occurs in each row which throws off the average. In other words, =IF(Status1 = "Inactive", 0, COUNTIF(EmpNumber:EmpNumber, EmpNumber1))
The above formula gives me the numbers, but the problem is that if someone has 10 certifications, they would have 10  10's in that column which would throw off the average.
I am not sure what I need to do with this information to get an accurate ratio of certifications per person. I appreciate anyone's assistance with this.
Comments

I'm missing some information to give a full answer.
With the formula you used, you have the total number of certificates for each employee. Bob will have 10, Mary 5, etc. But if you are copying this formula down the column (which is what you imply), then you are likely putting them in the wrong place.
If, instead, row 1 contained a 'summary row'  that is a row that will be used to consolidate your totals, then you would put Bob in the EmpNumber1 cell and your formula would still return 10 (because Status1 would be something besides "Inactive"). Each employee will have their own row in the 'summary section'
(I would move this to row 2 and have a parent row for the summary section  you'll be able to collapse / semihide it and it will be easier for the sum/counts to come)
I'm assuming that Bob is only at one site/area.
There must be somewhere on the rest of the sheet where we know Bob is from site/area "Area 51".
In the Summary section you'll be able to get a total of number of certificates by site/area.
You'll need to count how many people are in each area.
Each area will then be able to determine average certificates / per person.
I hope that is clear and helps
Craig
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives