Count, sum, average

meekerc26096

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.

J. Craig Williams

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 / semi-hide 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