Hi,

When working with array functions, I often encounter the following problem:

Lets say I have a dataset where columns B through E represents year 2000
through 2003 (entered in row 1) and rows 2, 3, 4, 5 represents North, South,
East, and West (entered in column A). The range of the data populating the
table thus covers B2:E5. All observations are positive integers. Except for
2000 West (D5), which is blank (for the purpose of the example, which you
will see below).

Using Boolean logic, I can pick up the data point that represents East and
2002 by entering the following array formula
{=SUM(B2:E5*(2002=B1:E1)*("east"=A2:A5))}. Furthermore, I could, for
instance, count all of the East and West observations by entering
{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2 :A5)))}, which returns 7
(as D5 is blank). Rather than counting, I could find the sum of the same
range by changing the formula to
{=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east "=A2:A5)))}, which returns
the SUM of the 7 observations -- in my dataset, that sum is 64 (I am aware
that the ISNUMBER(B2:E5) is redundant).

Here is my problem: Let's imagine that rather than being blank, cell D5
actually has ="" entered into it. The formula
{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2 :A5)))}, still returns 7.
However, let's say that rather than counting the cells, I want find the sum
and I thus substitue the "1" with the full range of data (B2:E5) (just as
demonstrated above), so that the formula looks like
{=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east "=A2:A5)))}. This formula
returns an error (#VALUE!). Can anyone explain to me how they would go about
solving this problem. The solution should not be to change the contents of
cell D5). Neither should the solution be SUM(B4:E5). The point is that "west"
and "east" would usually be linked to an input that might change. This issue
has been bugging me for a while, and I really hope to find a solution.

Thanks,
Henrik

You'll need IF to eleiminate text values like ""...

=SUM(IF(ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2 :A5)),B2:E5))

Henrik wrote:
Hi,

When working with array functions, I often encounter the following problem:

Lets say I have a dataset where columns B through E represents year 2000
through 2003 (entered in row 1) and rows 2, 3, 4, 5 represents North, South,
East, and West (entered in column A). The range of the data populating the
table thus covers B2:E5. All observations are positive integers. Except for
2000 West (D5), which is blank (for the purpose of the example, which you
will see below).

Using Boolean logic, I can pick up the data point that represents East and
2002 by entering the following array formula
{=SUM(B2:E5*(2002=B1:E1)*("east"=A2:A5))}. Furthermore, I could, for
instance, count all of the East and West observations by entering
{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2 :A5)))}, which returns 7
(as D5 is blank). Rather than counting, I could find the sum of the same
range by changing the formula to
{=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east "=A2:A5)))}, which returns
the SUM of the 7 observations -- in my dataset, that sum is 64 (I am aware
that the ISNUMBER(B2:E5) is redundant).

Here is my problem: Let's imagine that rather than being blank, cell D5
actually has ="" entered into it. The formula
{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2 :A5)))}, still returns 7.
However, let's say that rather than counting the cells, I want find the sum
and I thus substitue the "1" with the full range of data (B2:E5) (just as
demonstrated above), so that the formula looks like
{=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east "=A2:A5)))}. This formula
returns an error (#VALUE!). Can anyone explain to me how they would go about
solving this problem. The solution should not be to change the contents of
cell D5). Neither should the solution be SUM(B4:E5). The point is that "west"
and "east" would usually be linked to an input that might change. This issue
has been bugging me for a while, and I really hope to find a solution.

Thanks,
Henrik