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
.
|