nested if loop with logical OR/AND

[sa02000]

I am trying to write a formula to do the following.

1. I am trying to sum 7 cells in a row
2. before that I Check those 7 cells if they have "-" or #N/A or
number using if(OR....)). see my formula below.
2. If OR returns true for either "-" or #N/A check then if returns
(number zero) otherwise it returns the cell value itself and then I a
adding all those returned values.
3. But in some cell I get good results but in some cells I get #N/A.
tried few different versions but none of them worked.

Please Help.


=IF((OR(O6="#N/A",O6="-")),0,O6)+IF((OR(P6="#N/A",P6="-")),0,P6)+IF((OR(Q6="#N/A",Q6="-")),0,Q6)+IF((OR(R6="#N/A",R6="-")),0,R6)+IF((OR(S6="#N/A",S6="-")),0,S6)+IF((OR(T6="#N/A",T6="-")),0,T6)+IF((OR(U6="#N/A",U6="-")),0,U6)

I tried replacing o6="#N/A" with ISERROR(O6) in all of the formula bu
that did not work either.

Is there some other better way to do what I am trying to do??

Thanks,
Ja

 

[swatsp0p]

I think you are over complicating your formula. A dash (or any text
entry) will be treated as a zero in the SUM function. You only need to
test for #N/A and that can be done as such:

=SUMIF(O6:U6,"<>#N/A")

Does that work for you?

 

[Tom Ogilvy]

=SUM(IF(ISNUMBER(O6:U6),O6:U6,0))
Entered with Ctrl+shift+Enter rather than just Enter (since this is an array
formula) should give you the sum.

 

[sa02000]

Works perfect (not that you didn't know that already )....Thank yo
very much.

Ja

 

[swatsp0p]

I'm glad it worked for you. Thanks for the feedback, it is alway
appreciated.

Cheers