Excel 2007 : Nested Offset Array within Sum or Sumproduct

I have an Offset function that returns an array list in the form {8, 6, 4, 2, 0, 0...}. I can see these results when I go into the formula and audit it with F9. I have nested this Offset formula within a Sum function to be able to sum the array values (in this case it should be 20) however the result I get is 8 (the first value). I enter it as an array with Ctrl-Shift-Enter. My eventual goal is really to nest this Offset within Sumproduct, however that returns #VALUE! I think for the same reason the Sum function is not working. I have tried other Offset functions within a Sum and those have the same issue - I am wondering if this is some kind of a known issue or some excel setting problem?

My sprdsheet is set up as follows: First column is "A" and first row is "1". 2, 4, 6, 8 are in B2:B5 (this is a subset of a much larger matrix) and 10, 20, 30, 40 are in C6:F6.

PHP Code: 

        1    2    3    4
1    2                
2    4                
3    6                
4    8                
         10      20      30      40 


Formula I have is: {=SUM(OFFSET($B$2,COUNTIF($C6:$X6,">0")-(C1:X1),0))}

Thank you for anyone that can help!

Mert

I have a bunch of investments in columns going to the right (every new investment assumed to be $100 here) and these investments go down with principal payments received over time (in rows). As principal payments come in, those get reinvested in subsequent months so as to keep the total portfolio balance constant. I am not showing all this here but a simplified $100 investment every month in columns (so assume every new investment really has a different balance).

Table values are invested balances (going down over time in rows - new investments are in columns)

PHP Code: 

    Durtn    1    2    3    4    5
1    6.1    100                
2    5.9    95    100            
3    5.8    90    95    100        
4    5.7    85    90    95    100    
5    5.5    80    85    90    95    100 


I am making an assumption that each investment has an average life (or duration) profile as laid out in Column B (starts at say 6.1 and declines with passing time). I am trying to calculate my portfolio duration every point in time (going down in rows). So, in Month 1, my $100's duration is 6.1. Month 2, my Month 1 investment is at $95 with a duration of 5.9 and Month 2 investment of $100 has a 6.1 duration. The weighted average formula would be ($95 * 5.9 + $100 * 6.1) / ($100+$95). And so on. This means, I have to invert the duration column numbers for the number of months I have made investments for - meaning for my portfolio duration calc in Month 2, I take the Top 2 duration column numbers and invert them to multiply with the Row 2 balances, in say Month 10, I would need to invert the Top 10 duration numbers and multiply with the Month 10 (in rows) balances.

The Offset function I created gives me these inverted durations beautifully, and a sumproduct of these with the balances would work, but I am not able to sum or sumproduct the results on the Offset array.

Thanks in advance for any suggestions. I am also curious, if anyone knows, why I cannot use the results of an offset array in sum or sumproduct. I have searched through other chat rooms and it seems some people have used them (those examples I found that supposedly work do not work for me in Excel 2007 or Excel 2010 - tried in both).

Mert

Thank you very much! Using the "N" function solved my issue - had to convert the array items to "numbers" using this "N" function.

For future use by others, attached is a spreadsheet that shows the exact calcs - I changed my formula a bit and I have not had a chance to look at the indirect formula you suggested (but your results do match mine for the numerator of the calc (I forgot to mention we have to divide the results of the sumproduct with the sum of the balances)).

Final array formula is as follows for the attached excel file (formula is copied down):

{=SUMPRODUCT(N(OFFSET($C$7, ((B7-(D$6:L$6))*(B7>=(D$6:L$6))*1), 0))*(D7:L7) ) / SUM(D7:L7)}

Thanks again for your invaluable help!

Mert