Excel - change cell value on sheet1 based on multiple cell criteria in sheet2 column range

I have 2 worksheets. In the first worksheet (sheet1) I have a cell that can have 3 possible text values of "Not Started", "In Progress", "Completed".

In the second worksheet(sheet2). I have a column range of cells that can have one of the following status set "Not Started", "In Progress", "N/A", "Completed".

Sheet1 Column A Cell A1 values (from a list) = "Not Started","In Progress","Completed"

Sheet 2 Column A Cell A1 values (from a list) = "Not Started","In Progress", "N/A", "Completed"

I need a formula that will automatically change Sheet1, Cell A1 text to "Not Started" if all of the cells in sheet2 Column A (range) are all set to "Not Started".

If one of those cells in sheet2 (Column A cell range) has a value of "In Progress", Sheet1 Cell A1 should change to "In Progress".

If all the Sheet2 (Column A cell range) match "Completed" or "N/A", the Sheet1 Cell A1 should say "Completed".

I think I need to combine a IF statements with CountIF statements, but not sure how to write this?

Thank you in advance! Tristan

Try

=IF(COUNTIF(Sheet2!A:A,"Not started")=COUNTA(Sheet2!A:A),"Not Started",IF(COUNTIF(Sheet2!A:A,"In Progress"),"In Progress",IF(SUM(COUNTIF(Sheet2!A:A,{"N/A","Completed"}))=COUNTA(Sheet2!A:A),"Completed","")))

Assumes data in Sheet2 starts in A1

Returns blank if none of the above are met.

That's perfect, except I need one more condition added.

If Sheet 2 has the following cell values, it shows blank on Sheet1
A1=Not Started
A2=Completed
A3=Not Started.

Under this scenario, It should change Sheet1 value to "In Progress".

And if I wanted to start it instead of A1 in Sheet2, do I simply modify Sheet2!A:A to be something like Sheet2!A3:A (for cell 3?)

Try this ..

=IF(COUNTIF(Sheet2!A:A,"Not started")=COUNTA(Sheet2!A:A),"Not Started",IF(COUNTIF(Sheet2!A:A,"In Progress"),"In Progress",IF(SUM(COUNTIF(Sheet2!A:A,{"N/A","Completed"}))=COUNTA(Sheet2!A:A),"Completed","In Progress")))

Default is "In Progress" rather than blank.

Change range to A3:A10000 or whatever you think your maximum row will be.

Thank you John so much. Works great!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi John,

Sorry just been testing this some more and it is still not quite working for all the conditions.

If sheet 2 has a row with "Not Started" and another row with "N/A", it current shows "In Progress". It should remain as "Not Started"

=IF(COUNTIF(Infrastructure!C3:C1000,"Not started")=COUNTA(Infrastructure!C3:C1000),"Not Started",IF(COUNTIF(Infrastructure!C3:C1000,"In Progress"),"In Progress",IF(SUM(COUNTIF(Infrastructure!C3:C1000,{"N/A","Completed"}))=COUNTA(Infrastructure!C3:C1000),"Completed","In Progress")))

I tried to modify the formula to change the first 2 conditions to also use the Sum(CountIF) formula but I get a #value error.


The conditions I think I need to add are:
If N/A or Not Started = Display Not Started
If N/A or Not Started or In Progress - Display In Progress (is there a way to evaluate 3 conditions?

I figured it out, the solution was:

=IF(COUNTIF('Infrastructure'!C3:C1000,"Not started")=COUNTA('Infrastructure'!C3:C1000),"Not Started",IF(COUNTIF('Infrastructure'!C3:C1000,"In Progress"),"In Progress",IF(SUM(COUNTIF('Infrastructure'!C3:C1000,{"N/A","Completed"}))=COUNTA('Infrastructure'!C3:C1000),"Completed",IF(SUM(COUNTIF('Infrastructure'!C3:C1000,{"N/A","Not Started"}))=COUNTA('Infrastructure'!C3:C1000),"Not Started","In Progress"))))