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>

Team Name	R?	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	Total
Round Value		50	60	70	100	90	20	40	30	60	50	50	10	90	80	30	40	20	100	20
Test 1	5	y	y	y	n	n	n	y	y	n	n	y	y	y	n	y	n	y	n	y	500
Test 2	0	n	n	y	n	y	n	y	n	y	n	y	n	n	y	y	y	n	y	n	-200

<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>
</tbody>

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 />