# Office 365 >  > [SOLVED] if,then,isna

## treymcgovern

I am trying to create a spreadsheet that will return results of "Room Complete" or "Room Not Complete"

IF all conditions are met by putting an X in the cell then "Room Complete"
IF all conditions are not met a combination of "X" and "O" in the cell then "Room Not Complete"
IF The cell contains NA i want it to be ignored and not used in the conditions. 
     (x,x,x,x,x,NA,xxx) should still return "room Complete"
     (x,x,x,NA,x,x,O) or (x,x,x,o,x,x,o) would return "room Not complete"











9


any help is appreciated. File attached for my sheet.

Thanks

----------


## martindwilson

=IF(COUNTIF(B4:Z4,"x")+COUNTIF(B4:Z4,"na")=25,"room complete","not complete") should do it

----------


## treymcgovern

> =IF(COUNTIF(B4:Z4,"x")+COUNTIF(B4:Z4,"na")=25,"room complete","not complete") should do it




Thanks,  I may not have worded my question right.

If the cell has NA in it.  It should still return "room Complete".
It should only return "room not complete" if any of the cells have a "0"

----------


## martindwilson

that does just that
there are 25 columns so sum of x if all x =25 
if 23 are x and 2 na then 23+2 =25
if any o then result will be less than 25
you could use
=IF(COUNTIF(B4:Z4,"o"),"room not complete","room complete") but that would return room complete if all cells blank or contained anything other than o
or you could get clever and use
=IF(SUM(COUNTIF(B4:Z4,{"x","na"}))=25,"room complete","room not complete")

----------


## treymcgovern

> that does just that
> there are 25 columns so sum of x if all x =25 
> if 23 are x and 2 na then 23+2 =25
> if any o then result will be less than 25
> you could use
> =IF(COUNTIF(B4:Z4,"o"),"room not complete","room complete") but that would return room complete if all cells blank or contained anything other than o



I tried that in the spreadsehet and i keep getting "room not complete"

----------


## treymcgovern

> I tried that in the spreadsehet and i keep getting "room not complete"




Nevermind i had a "o" in the sheet i missed.  Thanks for the help.  Ive been banging my head on the wall.

----------


## arlu1201

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED".  I will do it for you this time. 

In future, to mark your thread as Solved, you can do the following - 
Select Thread Tools-> Mark thread as Solved. 

Incase your issue is not solved, you can undo it as follows - 
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you.  This adds to the reputation of the person who has taken the time to help you.

----------

