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

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
Asked:
CDS-JBC
  • 4
  • 3
  • 2
1 Solution
 
redmondbCommented:
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
 
redmondbCommented:
... or even simpler for all...

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

Regards,
Brian.
0
 
CDS-JBCAuthor 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
CDS-JBCAuthor 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
 
redmondbCommented:
No problem...

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

Regards,
Brian.
0
 
GirardAndrewCommented:
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"))

Open in new window

0
 
CDS-JBCAuthor Commented:
Thank you so much.  
0
 
redmondbCommented:
Thanks, CDS-JBC!
0
 
GirardAndrewCommented:
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.

Please clarify and thank you.

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now