How can use the Countifs function with the Today function in the formula?
Hello,
I am not sure what I did wrong in the formula that I have created. When I physically count it, I would get 23 but the formula spits out zero. I want the formula to report the number of tasks that was within the last 30 days.
Here is my formula: =COUNTIF({Ready For Upload Range 4}, {Ready For Upload Range 2} <= TODAY(-30))
I am creating the formula on a separate sheet than the one with the information. I can post the two test sheets that I am currently using to figure this out before applying it to the actual sheets.
Thank you.
Best Answer
-
If you're looking for a range (between 30 days), then you'll need to add in a Start of the range and an End of the range. You currently just state the Start of the range (30 days ago), but there's no end for this (today).
Here's how you would set up a cross-sheet COUNTIFS (note the S to make it plural) to look through a date range:
=COUNTIFS({Ready For Upload Range 2}, >= TODAY(-30), {Ready For Upload Range 2}, <= TODAY())
Note that I swapped around your <= and >= as well.
>= TODAY(-30) checks to see if the date is greater than 30 days ago, instead of <= TODAY(-30) which looks to see if the date is less than 30 days ago (as in, earlier than 30 days ago).
If this doesn't work for you, it would be helpful to see a screen capture of the sheet you're looking into, with an explanation of any other criteria you're looking to count. I'm unclear as to what your initial range, Range 4, is, so that would be useful to know as well.
Thanks!
Genevieve
Answers
-
If you're looking for a range (between 30 days), then you'll need to add in a Start of the range and an End of the range. You currently just state the Start of the range (30 days ago), but there's no end for this (today).
Here's how you would set up a cross-sheet COUNTIFS (note the S to make it plural) to look through a date range:
=COUNTIFS({Ready For Upload Range 2}, >= TODAY(-30), {Ready For Upload Range 2}, <= TODAY())
Note that I swapped around your <= and >= as well.
>= TODAY(-30) checks to see if the date is greater than 30 days ago, instead of <= TODAY(-30) which looks to see if the date is less than 30 days ago (as in, earlier than 30 days ago).
If this doesn't work for you, it would be helpful to see a screen capture of the sheet you're looking into, with an explanation of any other criteria you're looking to count. I'm unclear as to what your initial range, Range 4, is, so that would be useful to know as well.
Thanks!
Genevieve
-
Hi @Genevieve P
Thank you for your help. Your formula worked. I needed to add the end date.
Kind Regards,
Johnathon
-
Glad to hear that it worked for you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!