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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!