IF to ignore #N/A

C

claytorm

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

Paul Sheppard

claytorm said:
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.
Click to expand...

Hi Bertie

Have a look at the ISNA function
 
J

Jay Somerset

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.
Click to expand...

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

jim314

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
 
D

Domenic

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!
 
Top