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
|