Needs help combining functions.
 

I have a worksheet I use every day at my job at a car dealership to keep track of the previous days paperwork. I have attached an image of the form.
On this worksheet Column B lists the advisor who was in charge of the repair order. For each repair order, the advisor must turn in a sheet of paper showing completed work. If they turned in that sheet with their repair order, I put an X in column D. If they did not, I put NO.
In a chart at the bottom of the worksheet, I calculate the percentage of how many people turned in the sheet overall. I also calculate each individuals turn in percentage.
I was going to use the Countif function =countif(Cell Range in Column D, "X")/Number of Cells for the overall percentage. For example: =countif (D5:D15, "X")/11
But the problem with this is the number of repair orders changes every day. I don't want to have to change the forumla every day. Is there anyway to combine countif with another function that will only count how many cells in column D are in use?
Also, what formula can I use to calculate the individual advisor's percentage? The amount of repair orders they are in charge of also changes every day. Is the a way to use a formula that only counts the cells that are next to a certain advisor?
I'm not sure if any of this is even possible. So, any help would be appreciated!

Needs help combining functions.
 

Given that your range starts in row5, why not make this a dynamic range
and use it as follows...

=COUNTIF(MyRange,"X")/COUNTA(MyRange)

...where countif will return the number of Xs and counta will return the
number of non-empty cells in the range. Making it a dynamic range
allows its boundaries to automatically adjust to the number of entries.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi gargamel875

In C22 & copy down: =COUNTIFS($B$5:$B$17,A22,$D$5:$D$17,"x")/COUNTIFS($B$5:$B$17,A22)
Format cells as "%"
I don't quite understand the second part. Probably me!

Kevin

Thanks so much for your help. What I mean by the second part is that the repair orders per advisor will vary every day. So in the example pic, I would use =countif(D1:D2, "x")/2 to get their percentage for that day. But if they have three repair orders the next day, I have to change the range to include the third cell. I don't know how to avoid this. Is there formula that when only count the X's in column D only if they are in the same row as advisor 1?