• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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