Formula for Appraisal form using checkboxes

[cathy2007]

Hi There,

I'm putting together a performance appraisal form with checkboxes. Picture tasks down the vertical, and checkboxes across the horizontal in a chart format. There are 7 rows (tasks), and 5 columns (rating points from 1-5). My grid looks like 35 checkboxes, with only one possible check in each row. I've linked the checkboxes to the adjacent cells so I have a TRUE/FALSE grid next to my checkbox grid.

I want to have the "overall" row at bottom automatically populate based on the boxes checked for "tasks" 1-7. What is the formula I need in the 5 boxes at the bottom 'overall' row if I want the following results; any check in column C gets 5 pts, in column D gets 4 pts, etc. and the key is:

5 overall = total of 32 to 35 pts
4 overall = total of 25 to 31 pts
3 overall = total of 18 to 24 pts
2 overall = total of 11 to 17 pts
1 overall = total of 1 to 10 pts

Thanks so much!!

Cathy

 

[Scott T]

Is this what you want? there would be check boxes in B2:F8.

	A	B	C	D	E	F	G	H	I	J	K
1	task	1	2	3	4	5
2	task1						FALSE	TRUE	FALSE	FALSE	FALSE
3	task2						FALSE	TRUE	FALSE	FALSE	FALSE
4	task3						TRUE	FALSE	FALSE	FALSE	FALSE
5	task4						TRUE	FALSE	FALSE	FALSE	FALSE
6	task5						TRUE	FALSE	FALSE	FALSE	FALSE
7	task6						TRUE	FALSE	FALSE	FALSE	FALSE
8	task7						FALSE	TRUE	FALSE	FALSE	FALSE
9
10
11	overall	5

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B11 =VLOOKUP(COUNTIF(G2:G8,TRUE)*5+COUNTIF(H2:H8,TRUE)*4+COUNTIF(I2:I8,TRUE)*3+COUNTIF(J2:J8,TRUE)*2+COUNTIF(K2:K8,TRUE)*1,{1,1;11,2;18,3;25,4;32,5},2) <tbody> </tbody>

<tbody>
</tbody>