• Status: Solved
• Priority: Medium
• Security: Public
• Views: 321

# IF function in Excel

I need to return a value of "complete" or "incomplete" in a cell if their is a value (any value) in a range of cells. Any help would be appreciated

0
CDS-JBC
• 4
• 3
• 2
1 Solution

Commented:
Hi, CDS-JBC.

If any values are found in any of the cells B1 to B10 then it returns "Complete", otherwise "Incomplete"...

=IF(COUNTIF(B1:B10,"<>"),"Complete","Incomplete")

Alternatively, the following only returns "Complete" if all cells have something in them...

=IF(COUNTIF(B1:B10,"<>")=ROWS(B1:B10),"Complete","Incomplete")

Regards,
Brian.
0

Commented:
... or even simpler for all...

=IF(COUNTIF(B1:B10,""),"Incomplete","Complete")

Regards,
Brian.
0

Author Commented:
I forgot to mention a hick up in this.   What I have is a check list and if all of the items are complete then the value I need to show in my cell is "complete", but if all of the items are complete except for the last column then I need to show the value "complete-no approval', but if any of the columns before the last one are empty it would just say "incomplete".

Sorry this has become more difficult than originally.  I hope you are able to help.
0

Author Commented:
I forgot to mention a hick up in this.   What I have is a check list and if all of the items are complete then the value I need to show in my cell is "complete", but if all of the items are complete except for the last column (correction - last cell) then I need to show the value "complete-no approval', but if any of the columns (correction - cells) before the last one are empty it would just say "incomplete".

Sorry this has become more difficult than originally.  I hope you are able to help.
0

Commented:
No problem...

=IF(COUNTIF(B1:B10,""),IF(AND(B10="",COUNTIF(B1:B10,"<>")-ROWS(B1:B10)=-1),"Complete-no approval","Incomplete"),"Complete")

Regards,
Brian.
0

Commented:
Hi CDS-JBC,

Please refer to the formula below or the solution attached.

I hope you will find my solution helpful.

Sincerely,
Girard Andrew Solution-27410474.xlsx
``````=IF(COUNTA(A2:J2)>9,"Complete",IF(COUNTA(A2:I2)=9,"Complete-No Approval","Incomplete"))
``````
0

Author Commented:
Thank you so much.
0

Commented:
Thanks, CDS-JBC!
0

Commented:
Hi CDS-JBC,

Did you consider the right solution? My assumption is that the checklist/data range is in a row and not a column like the other solution.

Sincerely,
Girard Andrew
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.