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

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:

• ✭✭✭✭✭✭

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

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

• ✭✭✭✭✭✭

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

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

• ✭✭✭✭✭✭

I use these two quite a bit when troubleshooting:

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

«1

• ✭✭✭✭✭✭

How is the Completed column populated?

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

• ✭✭✭✭✭✭

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

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

• ✭✭✭✭✭✭

Ok. And how is the Status column populated?

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

• ✭✭✭✭✭✭

Is the Status column a dropdown?

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

• Correct! Dropdown single select.

• ✭✭✭✭✭✭

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

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

• ✭✭✭✭✭✭

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.

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

• ✭✭✭✭✭✭

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

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

• ✭✭✭✭✭✭

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

10xViz.com

Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!