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 a number using if(OR....)). see my formula below.
2. If OR returns true for either "-" or #N/A check then if returns 0 (number zero) otherwise it returns the cell value itself and then I am adding all those returned values.
3. But in some cell I get good results but in some cells I get #N/A. I 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 but that did not work either.

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

Thanks,
Jay

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

--
Regards,
Tom Ogilvy






"sa02000" <[email protected]> wrote in
message news:[email protected]...
>
> 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 a
> number using if(OR....)). see my formula below.
> 2. If OR returns true for either "-" or #N/A check then if returns 0
> (number zero) otherwise it returns the cell value itself and then I am
> adding all those returned values.
> 3. But in some cell I get good results but in some cells I get #N/A. I
> 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 but
> that did not work either.
>
> Is there some other better way to do what I am trying to do??
>
> Thanks,
> Jay
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile:
http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=475021
>

[sa02000]

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

Jay

[swatsp0p]

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

Cheers!