Determine Employee Participation Rate with Multiple Variables

**eliwaite** · 08-06-2014, 03:52 PM

I am trying to figure out employee participation in a given quarter. We measure participation by making at least one sale in the quarter. I have a list of employees and a list of sales such as this:

Employee
Jon
Jane
Bill
Jill
Jack

Sales Order # Month of Sale Employee
1 January Jane
2 January Jack
3 February Bill
4 February Bill
5 March Jack

I would like the output to look like this:

January February March
40% 60% 60%

I thought about using CountIfs, such as =countif(Month of Sale,"January")/rows(Employee) and =countifs(Month of Sale,"January",Month of Sale,"February")/rows(Employee) and so forth. The issue is with this is if a salesperson makes more than one sale in a month they get double counted, inflating the participation rate. Need someway to tell Excel to only count an employee once per month.

Ideas? Thanks.

**hoyasaxa215** · 08-06-2014, 03:55 PM

Where are you percentages coming from?

**eliwaite** · 08-06-2014, 04:14 PM

In the Employee Field, I have 5 employees (Jon, Jane, Bill, Jill and Jack)

For the month of January there were two sales one by Jane and one by Jack, thus we had 2 of the 5 employees "participating", 2/5 = 40%
For the month of February there were two sales but they were both by Bill. As we are keeping a running total, we add Bill to Jack and Jane from January, as a result 3 of the 5 employees "participated", 3/5 = 60%
For the month of March there was one sale by Jack (which I realize I did my math wrong, the OP has been edited), seeing as Jack already had a sale in January there are still only 3 of the 5 employees "participating 3/5 = 60%