Typically I've always been able to find an example of anything I run into using Excel. However this time I can't find exactly what I am trying to do and am wondering if it is even something that excel can do:
In the example I created 2 different scenarios. I am only trying to populate 1 field - Overall Status. In the first example, Overall Status should populate "Pass" as long as the 2nd table example contains only Pass or N/A. As you can see in the 2nd example table the Overstatus is populated with "Fail", because one of the fields under the Pass/Fail/NA column detected a Fail.
Also, if any of the fields in the Pass/Fail/NA cells is blank and not populated with Pass/Fail/NA then the Overall Status should populated Fail as blank is not allowed.
Is this even something that Excel can do. I keep finding populating multiple fields and it keeps dragging down.
I tried playing around with, vlookup, xlookup, if statement. they would either read the first pass or it would populate multiple fields under the Overall Status. I'm only expecting one response under Overall Status.
=IF(SUM(COUNTIF(C4:C8,{"","Fail"})),"Fail","Pass")
Countif checks for the number of found blanks (non-values) and Fail
if any is found it returns Fail
, else Pass
.