SOLVED

New Contributor

# Filter certain project for which everything is finished

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

9 Replies
best response confirmed by DieterW745 (New Contributor)
Solution

# Re: Filter certain project for which everything is finished

``=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.

# Re: Filter certain project for which everything is finished

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

# Re: Filter certain project for which everything is finished

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.

# Re: Filter certain project for which everything is finished

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

# Re: Filter certain project for which everything is finished

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

# Re: Filter certain project for which everything is finished

=IFNA(UNIQUE(FILTER(A2:A6,A2:A6<>FILTER(A2:A6,(C2:C6="nok")+(D2:D6="nok"),""))),"")

# Re: Filter certain project for which everything is finished

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)

# Re: Filter certain project for which everything is finished

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.

# Re: Filter certain project for which everything is finished

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.