nested if loop with logical OR/AND
 

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

--
sa0200
-----------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=47502

nested if loop with logical OR/AND
 

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?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475021

nested if loop with logical OR/AND
 

=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" wrote in
message ...

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

nested if loop with logical OR/AND
 

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

Ja

--
sa0200
-----------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=47502

nested if loop with logical OR/AND
 

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

Cheers

--
swatsp0

-----------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...fo&userid=1510
View this thread: http://www.excelforum.com/showthread.php?threadid=47502