Countif for odd columns with vllookup? Not sure on the best way to proceed

This is a a bit of a complex problem that i really would like to sort out but I'm not quite sure the best way to handle it. Let me first explain the problem. In Sheet1, I have a list of substitutions. For example, in the calculation I want to perform, I want 1 to be interpreted as 0.8, 2 to be interpreted as 0.5, and 3 to be interpreted as 0.1. Sheet1 is very simple and is literally no more complex than what is shown.

Sheet1:
A, B
1, 0.8
2, 0.5
3, 0.1

In Sheet2, the data I want to process is found in alternating columns. Below is an example, however the real Sheet2 is more extensive in size.

A, B, C, D, E, F
1, x, 2, x, 1, x
2, x, 1, x, 2, x

I want a formula (which would be placed in Sheet2 where the x's are now) that looks at the row (actually it's C1:ZZ1) and counts every other column using the substituted values found in Sheet1. In this example, I want the first x in the first row to produce:
0.8+0.5+0.8=2.1

If that weren't complicated enough, I actually need to do some additional math (I need the Sheet1 value for the cell to the left to be divided by the 2.1). In this case it would be 0.8/(0.8+0.5+0.8)=0.38 . Likewise, the second x in the first column would be 0.5/(0.8+0.5+0.8)=0.24 and so on.

The more I get into it, the more complicated it seems to get. One of the issues that I can't get around is that I need to be able to expand the number of columns in the future without breaking the formula (that's why I suggested going all the way to ZZ1). I was thinking INDIRECT potentially would allow me just to look for values relative to the cells location (to the left) and allow me to use a single formula for all x's above but it produced a mind numbing effect and I promptly stopped.

An idea I've tossed around without success: using COUNTIF to count the number of 1's 2's and 3's in a row (odd columns only), multiplying by the respective values in Sheet1 and adding those products together (then dividing the contents of the lefthand cell by that sum that was created). This would avoid having to use vlookup which was my first instinct but I couldn't get it to play friendly with other functions.

Anyway, I'm at the tearing out my hair stage - I would certainly appreciate some help if anyone can help me solve this puzzle.

Can you post an example workbook, showing some of your data and the expected output?

Test.xlsx

Thank you for helping! I cleaned up a test workbook and added some text boxes that help describe the scenario. If any of it is unclear, I'd be happy to clarify.

Wow, you've set yourself a real challenge here, haven't you?

First off, I don't think you can ever have a generic formula for x number of columns solely contained within the columns of your call sheet, because any range used by such a formula is always going to include the cell that's performing the calculation, leading to a circular reference error.

So, in the event, the only way I could see to do it was to have another sheet which contains just the cells we're calculating on, and reference the range in that sheet instead.

How's this for you?

Thank you all so much for the help! I'm absolutely blown away by the awesome complexity of minds here! Can anyone think of a way around the circular reference error by:

• structuring the formula to avoid a range by referencing progressive cells in the row directly (using some INDIRECT and ADDRESS wizardry)
• referencing the whole row rather than a range and excluding column A and all even columns. I'm not sure how the circular reference error logic is triggered exactly so this might be a shot in the dark.

@icestationzbra:
I have tried what you suggested and the result for D5 becomes 10.02506266 when it should be 1.503759398. Due to the fact this document must be opened by others, enabling iterative calculations is not desirable. Do you think there is another way?

@Andrew-R:
You got D5 to be 1.503759398 so that's at least a start! I was playing along the same lines but abandoned it because I really wanted to keep the sheet as simple as possible (in appearance) and force the cell to do all the heavy lifting. There would be a sheet for every day of the month so it's already a behemoth and I want to try to keep it trim.

@olasa:
I'm sorry but my explanation wasn't very clear. D5 should be 1.503759398=(200/19)*(0.3/(0.3+0.6+0.6+0.6)). I know this is a tough one, thanks to all for giving it a shot!

I just wanted to thank everyone for your immense help! I ended up going with olasa's solution:
=($E$1/COUNTA($B$5:$B$23))*N(OFFSET(Stats!$B$1,C5,))/SUM(N(OFFSET(Stats!$B$1,N(OFFSET($C5,,(ROW(INDIRECT("1:"&COLUMNS($C5:$AF5)/2))-1)*2)),)))

I am not all that familiar with array formulas - can anyone help explain exactly what the portion in red is doing? It's a bit beyond my current experience but works beautifully. I know the ROW function is often used in arrays but it doesn't make intuitive sense to me.

Thanks again, everyone!