Update status in cell based on response in other cell

[Shp1]

Hi guys,

I am trying to get a cell to say "complete" or "not complete" based on yes or no answers provided in a form (cells b3:b5 and cells b8:b10 for arguments sake).

I was trying to do the formula with colours instead of words as I have conditional formatted the section headings in the form to green when all of the answers are answered yes but understand excel wouldn't be able to support this so trying to formulate with the responses across the entire form.

Does anyone know of a formula that could help?

Thank you in advance!

 

[Joe4]

You can use COUNTIF or COUNTIFS formulas to count the number of "yes" cells.
You can use this formula in either Conditional Formatting or a normal Excel function, i.e.

=IF(COUNTIF(B3:B5,"yes")=3,"complete","not complete")

 

[Shp1]

Hi Joe4,

This is the error I'm getting when trying to apply it across the many cells in the form:

 

[Joe4]

That is not a valid formula. The COUNTIF function only has 2 arguments (it has exactly ONE argument for the range).
The COUNTIF and COUNTIFS formulas only support continuous ranges. So you would have to do something structured like this:

=IF(COUNTIF(B4:B7,"yes")+COUNTIF(B10,"yes")+COUNTIF(B13:B14,"yes")+...+COUNTIF(B47:B54,"yes")=31,"complete","not complete")

(note the "..." is for you to continue the pattern of adding more COUNTIF functions for the other ranges)

However, if the cells in column B you re NOT checking (i.e. B8, B9, etc), will NEVER have "yes" in them, then you don't need to break your range apart and could just do something like:

=IF(COUNTIF(B4:B54,"yes")=31,"complete","not complete")

It does not hurt to include those other cells if they will never have "yes" in them.

 

[Shp1]

Works perfectly, thanks Joe4!!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Shp1]

Hey Joe4, sorry but I have one more question.. if I wanted to change the formula so that it accepted a response of Yes and N/A to mark the form as complete then how should I change the formula?

 

[Joe4]

Hey Joe4, sorry but I have one more question.. if I wanted to change the formula so that it accepted a response of Yes and N/A to mark the form as complete then how should I change the formula?

Try something like this:

=IF(COUNTIF(B4:B54,"yes")+COUNTIF(B4:B54,"N/A")=31,"complete","not complete")

 

[Shp1]

Thanks!

 

[Shp1]

So, I've amended it but it doesn't seem to work for some reason - apologies but can you see where I might be going wrong with it?