IF to ignore #N/A

Hi,

I have a string of IF conditions, which I am combining with SUM. I want to get the IF conditions to ignore any cells containg the error message #N/A. How can I do this?

Thanks,
Bertie.

On Wed, 24 Aug 2005 11:07:13 -0500, claytorm
<[email protected]> wrote:

>
> Hi,
>
> I have a string of IF conditions, which I am combining with SUM. I want
> to get the IF conditions to ignore any cells containg the error message
> #N/A. How can I do this?
>
> Thanks,
> Bertie.

SUM should ignore cells within its range that are #N/A. Your combining SUM
with IFs may be confusing its normal behavior. If you could provide an
example of your formula, it might make it easier to find a simple solution
for you. Also, as someoner else mentioned, ISNA is a function that might
also help out here.
--
Jay.
(remove dashes for legal email address)

To sum a range housing #N/A's and numbers, try:

=SUMIF(Range,"<>#N/A")

claytorm wrote:
> Hi,
>
> I have a string of IF conditions, which I am combining with SUM. I want
> to get the IF conditions to ignore any cells containg the error message
> #N/A. How can I do this?
>
> Thanks,
> Bertie.
>
>

What if the range is as follows: B3, B5, C8, D12, E32

How do you enter that into the "Range" part of the SumIf?

Thanks,

Jim

"Aladin Akyurek" wrote:

> To sum a range housing #N/A's and numbers, try:
>
> =SUMIF(Range,"<>#N/A")
>
> claytorm wrote:
> > Hi,
> >
> > I have a string of IF conditions, which I am combining with SUM. I want
> > to get the IF conditions to ignore any cells containg the error message
> > #N/A. How can I do this?
> >
> > Thanks,
> > Bertie.
> >
> >
>

Try the following...

=SUM(IF(ISNUMBER(CHOOSE({1,2,3,4,5},B3,B5,C8,D12,E32)),CHOOSE({1,2,3,4,5}
,B3,B5,C8,D12,E32)))

....confirmed with CONTROL+SHIFT+ENTER, or...

=SUM(IF(ISNUMBER(N(INDIRECT({"B3","B5","C8","D12","E32"}))),N(INDIRECT({"
B3","B5","C8","D12","E32"}))))

....or, let A1:A5 contain B3, B5, C8, D12, and E32, and try...

=SUM(IF(ISNUMBER(N(INDIRECT(A1:A5))),N(INDIRECT(A1:A5))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
jim314 <[email protected]> wrote:

> What if the range is as follows: B3, B5, C8, D12, E32
>
> How do you enter that into the "Range" part of the SumIf?
>
> Thanks,
>
> Jim
>
> "Aladin Akyurek" wrote:
>
> > To sum a range housing #N/A's and numbers, try:
> >
> > =SUMIF(Range,"<>#N/A")
> >
> > claytorm wrote:
> > > Hi,
> > >
> > > I have a string of IF conditions, which I am combining with SUM. I want
> > > to get the IF conditions to ignore any cells containg the error message
> > > #N/A. How can I do this?
> > >
> > > Thanks,
> > > Bertie.
> > >
> > >
> >

You can use If and ISNA to direct the formula what to do if it encounters an #NA