Forum Discussion

Atom0aks's avatar
Atom0aks
Copper Contributor
Dec 28, 2023

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 re...
excel
Formulas and Functions
Atom0aks's avatar
Atom0aks
Copper Contributor
Dec 28, 2023

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.

Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 28, 2023
OK, I see. What version of Excel do you run?
  • Atom0aks's avatar
    Atom0aks
    Copper Contributor
    Dec 28, 2023
    Microsoft® Excel® for Microsoft 365 MSO (Version 2309 Build 16.0.16827.20166) 64-bit
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Dec 30, 2023

      Atom0aks 

      Is everything fine with the above formula? Any issue?

      • Atom0aks's avatar
        Atom0aks
        Copper Contributor
        Dec 31, 2023

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

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Dec 29, 2023

      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. https://answers.microsoft.com/en-us/msoffice/forum/all/use-tables-not-ranges/992a6e69-9c06-4b01-9230-1e12a87b81b2 provide dynamic ranges...

Resources