**MNGuy**Jan 26, 2013 1:40 AM

A football game has 4 quarters so Q1, Q2, Q3, Q4. I'd like to keep track of scoring for the Home and Away teams with 8 columns:

HQ1, HQ2, HQ3, HQ4, AQ1, AQ2, AQ3, AQ4

The 8 columns above would have the scores input via letters defined in a "Score Key" table #2. The score key table contains 3 columns:

Safety, S, 2

Field Goal, F, 3

Point After, P, 1

Touch Down, T, 6

Conversion, C, 2

So if the Home Team scores a touch down (6), a point after (1), and a field goal (3), in the first quarter, the string in HQ1 = TPF

The above value for HQ1 = TPF could happen again for HQ2, HQ3, and HQ4.

Using the "concatenate" function would return the string TPFTPFTPFTPF. A human can sum this string 6+1+3+6+1+3+6+1+3+6+1+3=40

I've got the "lookup" function using the "right" function processing the numerical values for the last letter (right) in the concatenated string above, so F=3

Getting the other values and adding them is done using the "mid" function and subtracting 11 times for each string location, This is what I've got:

=SUM(

LOOKUP(RIGHT(T(CONCATENATE(A2, B2, C2, D2))),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-11,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-10,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-9,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-8,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-7,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-6,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-5,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-4,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-3,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-2,1),Table 2 :: B2:B6,Table 2 :: C2:C6)

LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-1,1),Table 2 :: B2:B6,Table 2 :: C2:C6))

The above sum returns the correct value for the case when the home team scores a TPF in each quarter, totaling 40 points or 12 total score items.

Though it works for the situtation above the Mid function blows up when the 2nd arguement is not greater than or equal to 1, so is there a way to do this so the sum wouldn't have to be edited for every combination of scores?