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

Ryan Narlock

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

Paul Newcome

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

Paul Newcome

Try thinking of dates as numbers where today is zero, tomorrow is one and yesterday is negative one. If you think about it that way, then what you are trying to accomplish is counting dates that are less than (today - 90 days).

=COUNTIFS(Completed:Completed, @cell <= TODAY(-91))

Paul Newcome

I use these two quite a bit when troubleshooting:

Functions List | Smartsheet Learning Center

https://help.smartsheet.com/functions

Smartsheet includes common functions found in most desktop spreadsheet programs. Here's the complete list including definitions and examples.  

Formula Error Messages | Smartsheet Learning Center

https://help.smartsheet.com/articles/2476176-formula-error-messages?_ga=2.66067626.529420994.1554725353-1302373248.1552411124

A formula will return an error when Smartsheet expects different elements from the formula than what are provided.

Paul Newcome

How is the Completed column populated?

Ryan Narlock

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 Newcome

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

Ryan Narlock

Already confirmed.

Paul Newcome

Ok. And how is the Status column populated?

Ryan Narlock

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 Newcome

Is the Status column a dropdown?

Ryan Narlock

Correct! Dropdown single select.

Paul Newcome

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

Ryan Narlock

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

Ryan Narlock

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 Newcome

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

Ryan Narlock

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

Paul Newcome

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