Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

# COUNTIFS comparing columns for overdue dates

Copper Contributor

# COUNTIFS comparing columns for overdue dates

Hi,

My objective is to count all overdue dates in 2023,

 E F Due Delivered 08-Aug-22 07-Dec-22 14-Dec-22 02-Feb-23 29-Jul-22 11-Aug-22 11-Nov-22 29-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

# Re: COUNTIFS comparing columns for overdue dates

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

in H2:

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

# Re: COUNTIFS comparing columns for overdue dates

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.

# Re: COUNTIFS comparing columns for overdue dates

OK, I see. What version of Excel do you run?

# Re: COUNTIFS comparing columns for overdue dates

Microsoft® Excel® for Microsoft 365 MSO (Version 2309 Build 16.0.16827.20166) 64-bit

# Re: COUNTIFS comparing columns for overdue dates

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

# Re: COUNTIFS comparing columns for overdue dates

Is everything fine with the above formula? Any issue?

# Re: COUNTIFS comparing columns for overdue dates

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

# Re: COUNTIFS comparing columns for overdue dates

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?

# Re: COUNTIFS comparing columns for overdue dates

@Atom0aks Could you post an update please?