COUNTIFS comparing columns for overdue dates

Copper Contributor



My objective is to count all overdue dates in 2023,




The result should be "1" but Excel returns a "0" when I use this formula,




Any help would be greatly appreciated. Thanks!

9 Replies

Hi @Atom0aks 


Could you try the following & let me know what doesn't work (something isn't clear to me)?


in H2:

  F:F,">=" & DATE(2022,12,31),
  F:F,"<=" & DATE(2024,1,1)


Your formula counts the number of dates in 2023. If you add another row of data (e.g. Column E, 31-Dec-23; Column F, 13-Oct-23), the formula will return "2" when only 1 row of dates is overdue.

I need an additional criteria counting delivery dates in 2023 that are greater than the due date.

OK, I see. What version of Excel do you run?
Microsoft® Excel® for Microsoft 365 MSO (Version 2309 Build 16.0.16827.20166) 64-bit



With 365 could you try (adjust E101 and F101 to your actual range):

    (F2:F101 >= DATE(2023,1,1)) *
    (F2:F101 <= DATE(2023,12,31)) *
    (F2:F101 > E2:E101)


As a generic recommendation (not specific to FILTER), avoid referencing a full column (i.e. E:E, F:F) when that's not actually required, this can lead to perf. issue. Tables provide dynamic ranges...


Is everything fine with the above formula? Any issue?



Sorry! I read through your response and it's taking me a bit of time to see if organizing my data as a table could give me the information I need rather than filtering but it seems I still run into the same problem (it counts all deliveries in 2023, not just the overdue).


it seems I still run into the same problem

Either it does, either it doesn't solve the problem :) If it doesn't could you post a sample with say 10-15 rows, highlighting in 1 color what must be counted, in another color what must be excluded?

@Atom0aks Could you post an update please?