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!

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?