Array Formula and #N/As ?

[Jay]

Hi,

I'm using the following formula but it's not working because my SUM
range E29:E59 contains #N/As. I know one solution is to add an IF
statement to the formulas in E29:E59 to return "" if not avialable but
I'm trying to find a olution that works *with* the NAs is the range.

=SUMPRODUCT(--(B29:B59>=B94),--(B29:B59<=C94),(E29:E59))

In any other array formula I'd try use IF and ISNUMBER but am not sure
if this is possible with Sumproduct.

Can anyone advise how I can amend this formula so that it works with
#N/As in E29:E59. Or, another forumla that does the same thing?

Many thanks,

Jason

 

[Bob Phillips]

=SUM(IF(ISNUMBER(E29:E59),IF((B29:B59>=B94)*(B29:B59<=C94),E29:E59)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jay]

=SUM(IF(ISNUMBER(E29:E59),IF((B29:B59>=B94)*(B29:B59<=C94),E29:E59)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Thanks Bob,

I'd figured a similar solution out, except I had the ISNUMBER check in
the same IF as the other two conditions and had an ,0 ELSE condition.

Can SUMPRODUCT not accomodate an ISNUMBER check in the same way?

Cheers,

Jason

 

[Bob Phillips]

The problem is that with SP, even adding an ISNUMBER check doesn't solve it
because it still evaluates the NA's in the range. It is not linear, whereas
with the IFs you force a linear evaluation. You can't add IFs to SP as that
will change the range sizes and return a #VALUE error.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jay]

The problem is that with SP, even adding an ISNUMBER check doesn't solve it
because it still evaluates the NA's in the range. It is not linear, whereas
with the IFs you force a linear evaluation. You can't add IFs to SP as that
will change the range sizes and return a #VALUE error.

Thanks for taking the time to explain it. That's been a real help in my
understanding of SP.

Regards....Jason