Trying to Count number of items with a date in the next 60 days
Morning!
I have a sheet that I want to count all of my projects starting in the next 60days. I have used this formula, but it's giving me the wrong answer. I know I have 6 jobs that start in the next 60 days (from today)
=COUNTIFS({Master - Jobs In Progress Anticipated}, @cell >= TODAY(60))
Where's the error? Thank you!
Best Answers
-
Yes, those are so easy to miss.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi - This formula has worked beautifully for me so I decided to try to pare down some metrics and now I'm wondering if it's possible to write this same type of formula, but instead of comparing only the criteria in one column( Anticipated Start Date, It would be comparing 2 different columns (Anticipated Start Date and Actual Start Date) and the criteria is the same in the next 60 days) Is that possible? Tried "IF" and "OR" in the formulas and not working so maybe not possible?
Answers
-
I think you're just using the wrong sign. Your formula is looking for things that are greater than or equal to a date that is 60 days past today. I think you just need to flip the sign.
=COUNTIFS({Master - Jobs In Progress Anticipated}, @cell <= TODAY(60))
-
I did that, too, and still getting incorrect answer. Just baffled!
-
I want to know items in the upcoming 60 days is the desire!
-
Ah, sorry. That last formula shows anything less than 60 days, it doesn't have a bottom limit. You need both.
=COUNTIFS({Master - Jobs In Progress Anticipated}, @cell >= TODAY(), {Master - Jobs In Progress Anticipated}, @cell <= TODAY(60))
-
AWWW! There we go! YAY! Thank you so much David!
-
OK - maybe you can sort this out! Trying to build on that formula above and only count items that are greater than 74%. Tried adding this to the formula and getting a big fat 0
{Master Bid List Range 6},
formula looks like this: +COUNTIFS({Master - Jobs In Progress Anticipated}, @cell >= TODAY(), {Master - Jobs In Progress Anticipated}, @cell <= TODAY(60)), {Master Bid List Range 6}, < "0.74"))
-
Again, I just think you've got the signs mixed up. You're formula says:
{Master Bid List Range 6}, < "0.74" --> which translates to "If the Master Bid Range is less than 74%"
If you're looking for items greater than 74% it would be the opposite symbol
=COUNTIFS({Master - Jobs In Progress Anticipated}, @cell >= TODAY(), {Master - Jobs In Progress Anticipated}, @cell <= TODAY(60)), {Master Bid List Range 6}, > "0.74"))
-
I have tried the signs multiple ways and cannot get the answer to populate. I even tried using "74%" instead of the "0.74%
Just not working and not sure why
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Nope - #unparseable
=COUNTIFS({Master Bid List Range 6}, @cell >= TODAY(), {Master - Jobs In Progress Anticipated}, @cell <= TODAY(60)), {Master Bid List Range 7}, < = 0.74%)
-
At a glance it seem like you need to add an @cell for the last part as well.
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks so much Andree! I just shared the sheet to you!
-
Also, remove the % symbol at the end of .74%. That will make it unparsable.
-
I noted that David and did remove, but still unparseable. It's crazy!
-
=COUNTIFS({Master Bid List Range 6}, @cell >= TODAY(), {Master - Jobs In Progress Anticipated}, @cell <= TODAY(60)), {Master Bid List Range 7}, @cell < = 0.74)
Says Unparseable?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!