SportDay Calculate Gold Silver Bronze

[sakijung]

Good morning. We will held sport day. And I would like some formular how to calculate the score for each prize. Please let see the data.
Sport----Type----Prize-----
a--------b--------c--------
100------1------- 1------- 7 (If we use the pattern for each prize and calculate.)
200------1------- 3------- 3
400------1------- 2------- 5
4x100----2------- 1------- 9
Total Score ------24-------

The criteria if type = 1 the prize scores are 1=7, 2=5, 3=3 and if type = 2, the prize scores are 1=9,2=7 and 3=5.
I would like the answer as the above example.
Now I use +(countif(c1:C4,1)*7)+(countif(c1:c4,2)*5).... I cannot separate type 1 or type 2 for each score.

Thank you very much.

 

[dave3009]

If there are no instances of any other values other than the ones stated

Excel Workbook
	A	B	C	D
1	100	1	1	7
2	200	1	3	3
3	400	1	2	5
4	4x100	2	1	9
5	Total Score			24
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D1	=CHOOSE(B1, CHOOSE(C1, 7, 5, 3), CHOOSE(C1, 9, 7, 5))
D2	=CHOOSE(B2, CHOOSE(C2, 7, 5, 3), CHOOSE(C2, 9, 7, 5))
D3	=CHOOSE(B3, CHOOSE(C3, 7, 5, 3), CHOOSE(C3, 9, 7, 5))
D4	=CHOOSE(B4, CHOOSE(C4, 7, 5, 3), CHOOSE(C4, 9, 7, 5))
D5	=SUM(D1:D4)

 

[sakijung]

Thank you very much for the answer. I try it already with 2003. It's OK. But I must hide or unhide the column next to. So sorry in column D E F G are for the another school team who race in this game. I don't want hide or unhide column. I want some formular to summarize at the bottom of each C, D, E, F....... And then Iwill rank who has the most points.

Thank you very much.