COUNTIFS Function - 0-30, 31-60, 61-90, >90 Days

04/30/21
Accepted

Hi,

I am looking to pull the total number of unassigned projects with 0-30, 31-60, 61-90, >90 Days. The "Completed" column is the date the form is submitted. I am getting a value of 0 when using the formula below. Thoughts?

=COUNTIFS(Status:Status, "Unassigned", Completed:Completed, AND(@cell >= TODAY(), @cell <= TODAY(+30)))

=COUNTIFS(Status:Status, "Unassigned", Completed:Completed, AND(@cell >= TODAY(31), @cell <= TODAY(+60)))

=COUNTIFS(Status:Status, "Unassigned", Completed:Completed, AND(@cell >= TODAY(61), @cell <= TODAY(+90)))

Tags:

Best Answers

Previous1

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    How is the Completed column populated?

    thinkspi.com

  • Hi Paul,

    Currently it's a manual populated date but once I have the formula down, will transition it the system auto created column based off when the form submission occurs.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    First thing I would double check is that the column is actually set as a date type column.

    thinkspi.com

  • Already confirmed.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. And how is the Status column populated?

    thinkspi.com

  • Status complete is systematically populated as new when the Smartform is submitted. Following review, I manually move the status to unassigned until it is assigned out to one of our staff.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Is the Status column a dropdown?

    thinkspi.com

  • Correct! Dropdown single select.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    OK. Next step is to make sure there aren't any spaces after "Unassigned" in the column properties.

    thinkspi.com

  • I another summary field counting number of "Unassigned" appropriately using the formula below so I don't think it getting caught in the status area.

    =COUNTIF(Status:Status, "Unassigned")

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Hmm... Try removing the Status range/criteria so that you are just doing the COUNTIFS on the date range. Then create a filter and filter the sheet based on that same date range.

    thinkspi.com

  • This formula appears to only be counting if it has todays date. If I put yesterday's date, it does not count it.

    =COUNTIFS(Completed:Completed, AND(@cell >= TODAY(), @cell <= TODAY(+30)))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Right. Because yesterday's date is less than TODAY(). Are you trying to count future dates or past dates?

    thinkspi.com

  • Just the past dates 0-30, 31-60, 61-90, 90 and <

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Ah. Ok. That's why you are getting unexpected results. Your formula is counting future dates. Try this instead...

    =COUNTIFS(Completed:Completed, AND(@cell >= TODAY(-30), @cell <= TODAY()))

    thinkspi.com

Sign In or Register to comment.