Array Formula and #N/As ?

  • Thread starter Thread starter Jay
  • Start date Start date
J

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
 
=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)
 
Bob said:
=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.
Click to expand...

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
 
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)
 
Bob said:
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.
Click to expand...
Thanks for taking the time to explain it. That's been a real help in my
understanding of SP.

Regards....Jason
 
Back
Top