Filter certain project for which everything is finished

[DieterW745]

Hello everyone,

I am searching for a way to do the following:

I have a table which looks like this:

project	part	check 1	check 2
Project A	1	OK	NOK
Project A	2	OK	OK
Project A	3	OK	OK
Project B	1	OK	OK
Project B	2	OK	OK

I want to show a table (on another sheet) which has the following result:

project

Project B

Because 'project B' is the only project with all checks on 'OK'

This sounds very easy, but i can't seem to find a solution.

Thank you all for your time,

Dieter 

[#2 · Accepted solution]

@DieterW745 

=LET(e,UNIQUE(A2:A6),ok,COUNTIFS($A$2:$A$6,e,$C$2:$C$6,"OK",$D$2:$D$6,"OK"),project,COUNTIF($A$2:$A$6,e),result,FILTER(e,ok=project),result)

Maybe with this formula which seems to work in my sheet. 

[#3]

Hey,

Thank you for your response, could you link me the original file, it doesn't seem to work for me.
It says the first COUNTIFS function has to many arguments ...

Thank you very much,
Best Regards,
Dieter Watelle

[#4]

@DieterW745 

I can send you the .pdf with the original file however it's in german. I can use formulas such as FILTER, UNIQUE and LET in Excel online but to my knowledge i can't attach these files to my post.

[#5]

Hey !
Just noticed, i made a translation error myself "English => Dutch", works perfect now, thank you !

[#6]

hi quadraple,
could you please let me understand filter part in above formula?

[#7]

Please try the formula below.
=IFNA(UNIQUE(FILTER(A2:A6,A2:A6<>FILTER(A2:A6,(C2:C6="nok")+(D2:D6="nok"),""))),"")

[#8]

@DieterW745 

I can't help feeling this looks somewhat complicated?

= LET(
    NOKλ, LAMBDA(aProject, OR(FILTER(checks,project=aProject)="NOK")),
    distinctProjects, UNIQUE(project),
    incomplete, MAP(distinctProjects,NOKλ),
    FILTER(distinctProjects, NOT(incomplete), "No complete projects")
  )

The first Lambda function is defined to accept a single project, filters the checks and looks for "NOK".

UNIQUE returns a list of distinct projects

MAP runs through each distinct project and returns TRUE if it is incomplete (using NOKλ).

FILTER selects projects that of not incomplete (i.e. completed)

@Quadruple_Pawn Does your Excel read the file despite language differences?

[#9]

@Peter Bartholomew 

The OR and NOT formulas are read and translated but the UNIQUE formula isn't read and translated.

However the whole LET formula can't be executed because i don't work with Office365.

[#10]

So that confirms that it is worth you posting Excel files rather than pdfs, since they will be translated by the receiving Excel version. Shame about the 365 issue though, 'ODER und NICHT' look a bit out of place within a sea of errors! Early on, in 2019, I gave up on traditional spreadsheets and moved entirely to Dynamic Array solutions that suited me far better. Prior to that, I had schooled myself to commit every formula with CSE but it was painful as a working environment.