Using your posted workbook,
try something like this:
C2: =SUMPRODUCT(COUNTIF(C3:C31,$A$4:$A$6)*E3)
Is that something you can work with?