SUM(IF( Array to avoid #NUM! values

[Elijah]

Hi again,

I posted a similar question a week or so back - but now I'm having
trouble with using the SUMPRODUCT or SUM(IF( type function, especially
when the ranges I'm referring to in these functions have #NUM!
references.

btw - thanks Frank, Aladin for your previous advice on a similar
question.

Using the SUMIF function somehow aviods this problem, but not when
summing using multiple criterias (ie. SUMPRODUCT or SUM(IF( array
type.

Can anyone help me in avioding the #NUM!? Is it possible?

Here is an example of the functions:

=SUMPRODUCT((RangeName1="Customer")*(RangeName2=1000)*TheSumRange)
This returns #NUM! - I guess because all of my ranges include #NUM!.

The other array type:
=SUM(IF(RangeName1="Customer")*(RangeName2=1000),TheSumRange)
This also returns #NUM!. I tried wrapping this with an ISERROR but
couldn't get it to work - eg.

=SUM(IF(ISERROR(RangeName1="Customer")*(RangeName2=1000),TheSumRange),,(RangeName1="Customer")*(RangeName2=1000),TheSumRange))

Assuming I want to leave the error terms within the list - can this be
done?

Again your help appreciated.

Elijah

 

[Frank Kabel]

Hi
post your used formulas for the range names
One importan thing: Don't use ranges such as A:A. Use A1:A65000 instead
(if you really need such huge ranges).

If your ranges itself contain a #NUM error post the formulas which
create these errors. You may change them to
=IF(ISERROR(your_formula)),"",your_formula)

 

[Biff]

Hi!

Do you have #NUM! errors in *ALL* the ranges? If your
errors are only in the sum_range:

=SUM(IF(rng1="C",IF(rng2=10,IF(ISNUMBER
(sum_rng),sum_rng))))

Array entered.

You'd be better off correcting the error problem, though.

Biff

 

[Elijah]

Ok - getting rid of the error is probably a better approach.

The errors come from an extracted list of customer names using the
array formula you provided before Frank:

{=INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA)-3),ROW(1:1)))}

I extended the formula down further than was neccessary to acomodate
when the customer list growths. So that's were the #NUM! errors come
into play. I guess I don't neccessarily need to do this - but I want
to automate my xls as much as possible for when others use it.

If there is a solution it would be helpful, - but I guess not the 'end
of the world'

I'd be happy to send you the spreadsheet if need be.

Elijah
ps. Biff I'll try your formula - when I remove the errors from the
list. thanks

 

[Frank Kabel]

Hi
use
=IF(ISERROR(INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA)-3),ROW(1:
1)))),"",INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA)-3),ROW(1:1))
))

Also entered as array formule

 

[Aladin Akyurek]

Ok - getting rid of the error is probably a better approach.

The errors come from an extracted list of customer names using the
array formula you provided before Frank:

{=INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA)-3),ROW(1:1)))}

I extended the formula down further than was neccessary to acomodate
when the customer list growths. So that's were the #NUM! errors come
into play. I guess I don't neccessarily need to do this - but I want
to automate my xls as much as possible for when others use it.

If there is a solution it would be helpful, - but I guess not the 'end
of the world'
...

Is there a particular reason for not implementing the formula system
suggested in

http://www.excelforum.com/showthread.php?t=276162

to extract the customer list of interest and associated values?

I'm saying this in view of the fact that you are attempting to use a
inefficient (slow), non-robust extraction formula which moreove
produces #NUM! errors that thwart further processing of the extracte
list. Expanding the formula in question with error trapping usin
ISERROR makes it doubly inefficient

 

[Elijah]

Hi Aladin,

I started to use your implementation - but I got confused with the
instructions you gave. I will definitely try it again to see if it overcomes
my current problems - and will post back to you. Do you have a prepared xls
example of the technique which you could email? ([email protected])

I opted for the quick (and possibly inefficient) fix because at the time I
had a deadline looming.
I am noticing though that my xls is considerably slower than previously.

Elijah

 

[Aladin Akyurek]

Elijah,

Example workbook is underway to you.

Cheers.

Aladin