Find latest date based on entry in other column

[Yumi]

In the sample sheet I have 3 columns. Tasks, Date, Completed. In B3 I use the formula =MAX(B5:B20) to find the latest date. In column C I marked the tasks already completed with “ok”. Want to modify my formula to find the latest date not completed.
Maybe combining =Max and =FILTER where C5:C20 <> “ok” but am unable to construct.

Date.ods (12.1 KB)

[Wanderer]

You may use SUMPRODUCT as a filter
.
Without testing

=MAX(
      SUMPRODUCT( C5:C20<>"ok"; B5:B20 )
      )

Compare to this case:

[Calc] how to sumproduct with match condition?

Let’s say I have 3 columns (A, B, C) and 10 rows (1 to 10). I want to SUMPRODUCT the values of the cells from columns B and C where values of column A match. For example: bla | 2 | 3 abc | 1 | 4 bla | 6 | 8 adb | 1 | 3 adb | 2 | 5 I want to have in D1 the SUMPRODUCT: 2*3 + 6*8; and 1*3 + 2*5 in D2

[sokol92]

or using a more specialized function:

=MAXIFS(B5:B20;C5:C20;"<>ok")

[Yumi]

Thank you for the solutions. The version with SUMPRODUCT throws an error which I did not evaluate further. MAXIFS does what I want. Formula solved.