Conditional Sum

[claude jerry]

I have a Excel File Saved on this below Link

http://www.savefile.com/files/1923737

A B C D E F G
Name Type Sun Mon Tue Wed Total
Joe Gold 5 5 2 12
Adam Silver 2 1 2 1 6
Sam Silver 3 3 2 2 10
David Blue 4 4 4 0 12
Sean Gold 3 3 3 2 11
Rod Silver 5 5 5 15

in Cell C:10 i have a Data Validation drop list =$C$1:$F$1

I want a formula which will give me the balance remaining of a particular
type depending on the selection in Cell C:10

Eg. If C:10 = mon

Balance Remain For = 7

It is much easier to understand if you can have a look at my excel file
saved on http://www.savefile.com/files/1923737

 

[claude jerry]

Typo

Eg. If C:10 = mon

Balance Remain For gold = 7

Eg. If C:10 = Mon (Selected from Drop Down List)

B11 = Gold C11 = 8 (Calculated with formula
=SUMPRODUCT(($B$2:$B$7=B11)*(INDIRECT($C$10)))
B12 = Silver C12 =9
B13 = Blue C13 = 4


I want a formula for balance remain in cell D11 for gold = 7