Return a value if the non-empty cells in a r contains text is equal-CodePudding

I have a table with the course status. I wanted to update the completion status as completed only by looking at the cell values that contain text and ignoring the blank cells. If all the cells in a row that contains text are equal to Completed then populate the completion status as completed. Otherwise not completed.

Expected Result:

I know how to calculate if all the fields have value with this formula (IF(AND(B2="Completed",C2="Completed",D2="Completed"),"Completed","Not Completed"). But not sure how to skip blank cells and consider all non-empty values in the above case.

CodePudding user response：

(IF(AND(OR(B2="Completed";ISBLANK(B2));OR(C2="Completed";ISBLANK(C2));...);"Not Completed")