G

#### Guest

I have encountered the following problem with array formulas, which is

described below (after the introduction that sets up an example).

Let's imagine a dataset where columns B through E represents years 2000

through 2003 (which are entered into row 1) and rows 2, 3, 4 and 5 represents

North, South, East, and West (which are entered into column A) so that the

data that populates the table covers B2:E5. Let's further imagine that cell

D5 is left blank. All values are positive integers.

Using Boolean logic and an array function, I can pickup the cross section of

East and 2002 by submitting the array formula

{=SUM(B2:E5*(2002=B1:E1)*("east"=A2:A5))}. Furthermore, I can count the

number of observations in East and West by submitting

{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))}, which returns 7

(keeping in mind that D5 is blank). Now, if I want to find the sum of east

and west, I would substitute "1" with B2:E5, so that my array formula looks

like {=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))} (which

with my unique dataset returns 64). I am aware that the ISNUMBER term is

redundant in calculation example.

Now, lets say that rather than being blank, cell D5 contains a formula that

returns "", such as ="". If I want to count the number of numerical

observations in East and West, I can still enter

{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))}, which returns 7.

However, and this is the problem, if I want to find the sum of East and West

and enter {=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2:A5)))},

Excel returns an error (#VALUE!). This problem has been bugging me for a

while and I would really appreciate a solution. The solution should not be to

change the contents of cell D5 or to simply take SUM(B4:E5). Usually I

wouldn't have entered "west" and "east" into the formula, but these would

actually reference some other input. It is important that the sum calculation

would dynamically update if, for instance, "east" was changed to "south". I

hope this makes sense and I appreciate any suggestions.

Thanks,

Henrik