Solved

# IF function in Excel

Posted on 2011-10-22
312 Views
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
Question by:CDS-JBC

LVL 26

Expert Comment

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

LVL 26

Expert Comment

... or even simpler for all...

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

Regards,
Brian.
0

Author Comment

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 Comment

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

LVL 26

Accepted Solution

No problem...

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

Regards,
Brian.
0

LVL 5

Expert Comment

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 Closing Comment

Thank you so much.
0

LVL 26

Expert Comment

Thanks, CDS-JBC!
0

LVL 5

Expert Comment

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

## Featured Post

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.