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.


--
claytorm
------------------------------------------------------------------------
claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=398660

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.

Hi Bertie

Have a look at the ISNA function


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=398660

On Wed, 24 Aug 2005 11:07:13 -0500, 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.

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.

IF to ignore #N/A
 

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.

IF to ignore #N/A
 

Try the following...

=SUM(IF(ISNUMBER(CHOOSE({1,2,3,4,5},B3,B5,C8,D12,E 32)),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 ,
jim314 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.

IF to ignore #N/A
 

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


--
jpx
------------------------------------------------------------------------
jpx's Profile: http://www.excelforum.com/member.php...fo&userid=1013
View this thread: http://www.excelforum.com/showthread...hreadid=398660