I need to count a total for a series of text vales and multiply that by a
number that appears in a a corresponding cell in an array of text.
The multiplying number is always relative to the text. Also not sure if it
affect things but the Text is derived from data verified drop down list.

eg:
COL 1 COL2 COL3 COL 4
MEL 2 ADE 3
MEL 1 MEL 2
SYD 3 SYD 1

I need a formula that would return the following counts for the example above;
MEL = 5
ADE = 3
SYD = 4