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.

[TABLE="class: grid, width: 400"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]task[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]task1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]task2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]task3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]task4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]task5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]task6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]task7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]overall[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]