Essentially what I'm trying to do is create a formula that will search three different ranges to determine the value of a cell using true/false statements.

I6 needs to return a value of "Complete", "Partially complete", or "Not complete" and it will be searching three ranges J7:J10 (COMPLETE), J11:J14 (PARTIALLY COMPLETE), J15:J19 (NOT COMPLETE).

Each range will have questions I7, I8, I9 and so on where the options are YES or to keep the cell blank.

What I'm trying to achieve is that IF J7:10 are all true THENI6 will display COMPLETE however IF any of J11:J14 are true THEN I6 will display PARTIALLY COMPLETE and IF any of J15:19 (can be just one cell) is true THEN I6 will display NOT COMPLETE

I've tried using COUNTIF, IF, VLOOKUP, ARRAY but I keep getting the dreaded #NAME

Any ideas?

CodePudding user response：

My understanding is the question in Columns I will be answered in Column J with either a "Yes" or leaving the cell blank. I highly recommend using Data Validation to limit unintended responses. Assuming my understanding is correct, you can solve this with nested IF statements and COUNTIF/COUNTBLANK statements. Excel will evaluate nested IFs from left to right, so you need to order these in importance of priority (e.g. what if someone has a "Yes" in both the Not Complete and Partially Complete section?). My suggestion would be:

=IF(COUNTIF(J15:J19,"Yes")>0,"NOT COMPLETE",IF(COUNTIF(J11:J14,"Yes")>0,"PARTIALLY COMPLETE",IF(COUNTBLANK(J7:J10)=0,"COMPLETE","THERE IS A DATA PROBLEM")))

This assumes that one "Yes" in the Not Complete section overrides anything else, and that the entire "Complete" section must be true to get a Completed result. Note that you'll get the Data Problem error if you have all blanks everywhere.