Forum Discussion

mikeuk's avatar
mikeuk
Copper Contributor
Apr 18, 2020

Help!! ** How to use Countif to count dates that occur today and before?

Hi All, 

 

Please Help, 

 

I need to be able to count the amount of dates in a column that occur today and then also count the amount between today and in the past by number of days (not date).

 

Can you help?

 

Thank you in advance

 

Mike 

excel
Formulas and Functions

4 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 18, 2020

    mikeuk 

    Mike,

     

    That's like

    =COUNTIF(A:A,TODAY())

    and

    =COUNTIFS(A:A,"<"&TODAY(),A:A,">="&(TODAY()-30))
    • thomasdegroot's avatar
      thomasdegroot
      Copper Contributor
      Oct 27, 2022

      SergeiBaklan 

      I am using =Countifs for a table that tells me how many records we have that were opened between 0-14 days, 15-29 days, and 30+ days. However, I only want to count those whose secondary column are equal to A or B and not C. 

       

      (0-14 days) =COUNTIFS(Table1[Date],TODAY(),Table1[Date],">="&(TODAY()-14))

      (15-29 days) =COUNTIFS(Table1[Date],"<"&TODAY()-14,Table1[Date],">="&(TODAY()-29))

      (30+ days) =COUNTIFS(Table1[Date],"<"&TODAY()-30,Table1[Date],"<="&(TODAY()-30))

       

      I thought I could do 
      =COUNTIFS(Table1[Date],TODAY(),Table1[Date],">="&(TODAY()-14), Table1[Status], "A" & "B") but this didn't seem to work. 

       

    • mikeuk's avatar
      mikeuk
      Copper Contributor
      Apr 18, 2020

      SergeiBaklan 

       

      Hi Sergei, 

       

      Thank you so much for your help!

       

      I have used the first one and it works perfectly! thank you

       

      But, I need the second one to display the amounts of dates in total that occur before today? 

       

      Thanks

       

      Mike

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 18, 2020

        mikeuk 

        Mike, if before today

        =COUNTIFS(A:A,"<"&TODAY())

         or you need number of dates, not number of records? For example, if today is Apr 18, and you have 3 records for Apr 16 and two records for Apr 17, above formula returns 5. If calculate only dates it shall be 2, please clarify what is required.

Resources