Aggr counts and statuses

[cbaqir]

I have a simple testing spreadsheet that has dimensions of Workstation, Test Case and Status. The same workstation can have several test cases. 

I am struggling with the best way to define these aggregated at the right level and how to get the Not Tested vs Incomplete expression:

For each workstation
If all testcase status = null then workstation status = not tested
else
If any testcase status = failed then workstation status = failed
Else if any testcase status = null then workstation status = incomplete
Else if any testcase status = ready for retest then workstation status = ready for retests
Else (all test case status = passed) then workstation status = passed

if(count({<Status+={"Not Tested"}>} distinct Workstation)>=1,'Not Tested',
if(count({<Status*={"Failed"}>} distinct Workstation)>=1,'Failed',
if(count({<Status*={"Ready to Retest"}>} distinct Workstation)>=1,'Ready to Retest',
if(count({<Status*={"Passed"}>} distinct Workstation)>=1,'Passed',
if(count({<Status*={"Not Tested"}>} distinct Workstation)>=1,'Incomplete')))))

[edwin]

expanding on your logic, these are Not Tested
=count({<Status={'Not Tested'}>}Workstation) =count(Workstation)

or using e():
=count({<[Test Case]=e({<Status={'Passed','Ready to Retest'}>})>}Workstation)
you just need to be careful in choosing the field to use in the e() function, such that this is not a field that is normally selected as filters (bec it overrides the filter)