Find "Incomplete" based on ID Number that doesnt repeat

[hmltnangel]

Hi excel peoples! One that I think should be so easy but my brains frazzled today. Its the blanks in the Staff ID column that are throwing me off

Column "A" has a list of Staff IDs. This ID number shows once for any employee, the next cells in the column are blank until the next staff id

A1 has staff id 1234567, a2-a5 are blank, then a6 will be the next one 1234568 and so on

Then Column B shows Sales Status on each row, "incomplete", "complete", "not started"

I want column C to show the worst overall status for any of the that employees sales as below.

Any help

Staff ID​	Completion​	Overall​
1234567​	Complete​	Incomplete​
	Not Complete​
	Complete​
	Complete​
1234568​	Complete​	Not Started​
	Not Started​
	Complete​
	Complete​
1234569​	Complete​	Complete​
	Complete​
	Complete​
	Complete​
12345689​	Complete​

 

[Joe4]

First off, I would fill all the blanks you have in column A using this easy method shown here: How to Fill Blank Cells with Value above in Excel (3 Easy Ways) - Spreadsheet Planet

Then, assuming your data is in the range A1:B14, place this formula in C2 and copy down for all rows:

=IF(A2<>A1,IF(COUNTIFS(A$2:A$14,A2,B$2:B$14,"Not Started")>0,"Not Started",IF(COUNTIFS(A$2:A$14,A2,B$2:B$14,"Not Complete")>0,"Not Complete","Complete")),"")

Note that you have an inconsistency in your first block. You have "Not Complete" in column B, but then show returning "Incomplete" in column C (which isn't even one of the options).