SUMIF Questions (I think!) 2

[mkuethe]

I'm needing to design a report that will score employees on their performance. I've tried several different possibilities, but cannot get any of them to work. I'll explain what I'm trying to do below:

There will be a total of 14 possible measures listed in 14 columns across the spreadsheet (Columns B thru O). The first 13 columns can receive a score of 1 thru 5 and the 14th column 1 thru 4. The first column (Column A) will be the employee's name. The last three columns (P, Q and R) need to be "Total Points", "Points Possible" and "Score".

What I'm having difficulty with is the "Points Possible" column. That column has to count the total possible score for each column, knowing that columns B thru N have a maximum score of 5, and that column O has a maximum score of 4. Also, not all employees are scored on every measure, so it needs to eliminate any columns left blank, or marked as N/A.

Can anyone please assist?!?!

 

[kphu]

This was tricky but i think i got what you want.

try this:

=((13-(COUNTIF(B4:N4,"&quot+COUNTIF(B4:N4,"N/A&quot))*5)+((1-(COUNTIF(O4,"&quot+COUNTIF(O4,"N/A&quot))*4)

paste this formula to cell Q4 and then copy to the other cells.

Let me know if it doesn't work or if you want an explanation.

ken

"Be sure to give a star to those who answer your questions correctly."

 

[KenWright]

=COUNTIF(B2:O2,">0&quot*5+COUNTIF(P2,">0&quot*4

Should do it I think. Assumes the cells not to be counted are blank or the N/A is text and not an Excel error message, eg #N/A.

Regards
Ken...............