This is more complicated than it sounds and I could really use some help. I have been working on just this one formula for days.... grrr

I am working on an Spread Sheet that contains about 50 names. The people on the list each pull a job from the system and process the job, then they enter the job status and product information into a tracker. I then pull the data from the tracker and enter it into my Spread Sheet. My spread sheet will then analyze and calculate how many jobs, they did that week.

Where does it get complicated. Well, when they enter the information into the tracker there are 4 different statuses they can choose from. They can choose, Completed, Non-Completed, Revised Completed, Revised Uncompleted.

My sheet breaks down not only how many orders they did, but what status they were. The problem occurs because when you look at the spread sheet, you will see the days of the week, then under it a name and to the right, the jobs in the 4 different status categories. When I try to do an average of jobs per per day, it is actually reading each one of the four categories as a day instead of recognizing that the four categories are actually one day.

I am attempting to First count 1 if they worked that day, then add the total amount of orders, then average how many orders per day they worked for the week. We work 7 days a week with two days off, Each employee having different days off.

Let me see if I can make an example

monday Tuesday AVE P/ DAY
COM RCO RNC UNC COM RCO RNC UNC
Joan 9 2 1 3 1 6 1 2 12.5

Please take note that it needs to look at all days and count how many days that person worked that week as some of them only work 3 or 4, while others work 5 days a week. Also if a person calls in sick I do not want to have to manually change how many days each person worked that week.


I would appreciate any help that you might be able to give me.


Thank you,
Lilbpaw