COUNTIFS comparing columns for overdue dates

Copper Contributor

Hi,

 

My objective is to count all overdue dates in 2023,

 

EF
DueDelivered
08-Aug-2207-Dec-22
14-Dec-2202-Feb-23
29-Jul-2211-Aug-22
11-Nov-2229-Dec-22

 

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

 

=COUNTIFS(F:F,">=31-Dec-22",F:F,"<=01-Jan-24",F:F,">"&E:E)

 

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)?

Sample.png

in H2:

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

Hi!

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

@Atom0aks 

 

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

=ROWS(
  FILTER(E2:F101,
    (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...

@Atom0aks 

Is everything fine with the above formula? Any issue?

@Lorenzo 

 

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).

@Atom0aks 

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?