Formula that sums the 2 far left numbers in a given range than the 2nd most left & so on

Hello all

Thank you so much for your help on creating formulas for my project.

Probably the last task I would like to do for my project is have a formula that sums up the 2 far left numbers in a given range than the 2nd most left than the 3rd & so on

On attached example, the range is labeled 1-13 (patt analysis 1, patt analysis 2) and the sum would be on result analysis

The numbers on any given column in a row can either be blank, -1, +1, or 0. In the given example on row 3, the far most left number is +1 and in the second range (columns N-Z) the far most left is -1...the resulting sum is on row 3 column AA which is zero.

The formula shall take the 2nd most far left now, which is row 3 column c & p which -1 & +1 respectively resulting in 0 on column AB. And the formula shall do the same for all the rest.

Note in row 11 (orange letters), I put in just to show the sum won't always be zero.

Thanks again and let me know if any further clarification is needed.

one option

Formula:

Please Login or Register  to view this content.

I'm not sure I followed why some rows included values on 2nd table devoid of an equivalent but others didn't...
the above will count n times, where n is max of count in both tables (should there be incomplete pair) - but you should, I hope, be able to adapt according to your needs.