Hi,<br>
I am trying to use Array Formulas. All the components seem to work fine, but when I then try to evaluate it in just one cell I get unexpected results. Let me give you some more details: I am using the following Array formula: {=IF($B$3:$BD$3=$E$1,HLOOKUP($B$1:$BD$1,$B$1:$BD$10,10,)*$B$7:$BD$7,)} , which returns exactly the results for each entry of the result. If I use the SUM function in a spreadsheet to sum all the values in the array, then I Excel displays the expected result. But if I wrap the SUM function over the Array and try to evaluate it in just one cell, then Excel only returns a 0. The Array Formula I am using now is as follows: {=SUM(IF($B$3:$BD$3=$E$1,HLOOKUP($B$1:$BD$1,$B$1:$BD$10,10,)*$B$7:$BD$7,))} . The confusing part now is that if I extend the Range of the Array to two cells, then the SUM function seems to be working fine as I get the expected value in both cells. My problem is that I definitely need the result to be returned in just one cell, and not over two (or more).Unfortunately I cant attach the problematic spreadsheet and I hope that the above description is understandable. Is there any feature of Array function that I am not aware of? Thank for any hint what might be going wrong. Thanks - Nevs<br>
I am trying to use Array Formulas. All the components seem to work fine, but when I then try to evaluate it in just one cell I get unexpected results. Let me give you some more details: I am using the following Array formula: {=IF($B$3:$BD$3=$E$1,HLOOKUP($B$1:$BD$1,$B$1:$BD$10,10,)*$B$7:$BD$7,)} , which returns exactly the results for each entry of the result. If I use the SUM function in a spreadsheet to sum all the values in the array, then I Excel displays the expected result. But if I wrap the SUM function over the Array and try to evaluate it in just one cell, then Excel only returns a 0. The Array Formula I am using now is as follows: {=SUM(IF($B$3:$BD$3=$E$1,HLOOKUP($B$1:$BD$1,$B$1:$BD$10,10,)*$B$7:$BD$7,))} . The confusing part now is that if I extend the Range of the Array to two cells, then the SUM function seems to be working fine as I get the expected value in both cells. My problem is that I definitely need the result to be returned in just one cell, and not over two (or more).Unfortunately I cant attach the problematic spreadsheet and I hope that the above description is understandable. Is there any feature of Array function that I am not aware of? Thank for any hint what might be going wrong. Thanks - Nevs<br>
