Trivia Night Score Sheet

[tly0227]

So I help run a Trivia night once a week and it's been getting pretty hectic, so trying to make my life a little easier and get Excel to do most of the scoring work for me. I initially just had a sheet where I just had to enter the point value of each question for each team and it'd autosum for me, which is cool...but I wanna do more.

What I'd like to do...

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="24"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="31"><col width="34"></colgroup><tbody>[TR]
[TD]Team Name[/TD]
[TD]R?[/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"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Round Value[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]20[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD="align: right"]5[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD="align: right"]500[/TD]
[TD="bgcolor: #00ff00"][/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD="align: right"]0[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD="align: right"]-200[/TD]
[TD="bgcolor: #00ff00"][/TD]
[/TR]
</tbody>[/TABLE]

Ideally, I'd like to write a formula for the total column (green cells) that if column 3-21 is a y, it sums the appropriate value for that round (row 2). In the sum, it also has to include column 2, which will either be +5 or 0 (bonus points for returning teams) and column 22 (round 20), which could be any number and may be positive or negative (is a bid amount..positive if they get the answer correct, negative if they are wrong).

I also have a version of this that's got the rounds on rows and each column is a team...if that version is better for what I'm wanting to do, I'm fine with that.

Can this be done?

 

[Michael M]

Is this what you're after ???

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Team Name	R?	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	Total
2	Round Value		50	60	70	100	90	20	40	30	60	50	50	10	90	80	30	40	20	100	20
3	Test 1	5	y	y	y	n	n	n	y	y	n	n	y	y	y	n	y	n	y	n	y	500	970
4	Test 2	0	n	n	y	n	y	n	y	n	y	n	y	n	n	y	y	y	n	y	n	-200	360
Sheet1

Cell Formulas
Range	Formula
W3	=SUMIF(C3:U3,"y",$C$2:$U$2)+V3
W4	=SUMIF(C4:U4,"y",$C$2:$U$2)+V4

style="text-align:left">=SUMIF(<font color="Blue">C4:U4,"y",$C$2:$U$2</font>)+V4</td></tr></tbody></table></td></tr></table><br />