How do I find/calculate the available (latest) date from multiple rows WHERE there is a condition

RedHat

Intent: Calculate date team member is available

I have a resource planning sheet, which lists a number of activities (by row) for team members. Each row describes the team member, activity, the status, and the start and end date.

How do I find the latest end date for each team member, where there activity is 'In Progress'?

In the attached screenshot, I want to calculate and store for:

Team Member 1 is available from: 17/08/23

Team Member 2 is available from: 30/06/23

Paul Newcome

You would have each team member listed out in a second sheet which would then allow you to use a MAX/COLLECT like so:

=MAX(COLLECT({End Date}, {Name}, @cell = Name@row, {Status}, @cell = "In Progress"))