SUM(IF( Array to avoid #NUM! values
 

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=10 00)*TheSumRange)
This returns #NUM! - I guess because all of my ranges include #NUM!.

The other array type:
=SUM(IF(RangeName1="Customer")*(RangeName2=1000),T heSumRange)
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")*(Rang eName2=1000),TheSumRange))

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

Again your help appreciated.

Elijah

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)

--
Regards
Frank Kabel
Frankfurt, Germany

"Elijah" schrieb im Newsbeitrag
om...
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=10 00)*TheSumRange)
This returns #NUM! - I guess because all of my ranges include #NUM!.

The other array type:
=SUM(IF(RangeName1="Customer")*(RangeName2=1000),T heSumRange)
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),TheSumRa nge))

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

Again your help appreciated.

Elijah

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

-----Original Message-----
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=1 000)
*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="Custo mer")*
(RangeName2=1000),TheSumRange))

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

Again your help appreciated.

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" wrote in message ...
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)

--
Regards
Frank Kabel
Frankfurt, Germany

"Elijah" schrieb im Newsbeitrag
om...
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=10 00)*TheSumRange)
This returns #NUM! - I guess because all of my ranges include #NUM!.

The other array type:
=SUM(IF(RangeName1="Customer")*(RangeName2=1000),T heSumRange)
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),TheSumRa nge))

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

Again your help appreciated.

Elijah

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

Also entered as array formule

--
Regards
Frank Kabel
Frankfurt, Germany

"Elijah" schrieb im Newsbeitrag
om...
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" wrote in message
...
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)

--
Regards
Frank Kabel
Frankfurt, Germany

"Elijah" schrieb im Newsbeitrag
om...
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=10 00)*TheSumRange)
This returns #NUM! - I guess because all of my ranges include
#NUM!.

The other array type:
=SUM(IF(RangeName1="Customer")*(RangeName2=1000),T heSumRange)
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),TheSumRa nge))

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

Again your help appreciated.

Elijah

Elijah Wrote:
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 I
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 an
inefficient (slow), non-robust extraction formula which moreover
produces #NUM! errors that thwart further processing of the extracted
list. Expanding the formula in question with error trapping using
ISERROR makes it doubly inefficient.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=318967

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? )

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" wrote in
message ...

Elijah Wrote:
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 I
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 an
inefficient (slow), non-robust extraction formula which moreover
produces #NUM! errors that thwart further processing of the extracted
list. Expanding the formula in question with error trapping using
ISERROR makes it doubly inefficient.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=318967

Elijah,

Example workbook is underway to you.

Cheers.

Aladin

Elijah Wrote:
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? )

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" wrote
in
message ...

Elijah Wrote:
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
I
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
an
inefficient (slow), non-robust extraction formula which moreover
produces #NUM! errors that thwart further processing of the
extracted
list. Expanding the formula in question with error trapping using
ISERROR makes it doubly inefficient.


--
Aladin Akyurek

------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread:
http://www.excelforum.com/showthread...hreadid=318967



--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=318967