# Trying to Count number of items with a date in the next 60 days

Options
✭✭✭✭✭

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!

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
edited 10/20/20 Answer ✓
Options

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?

«1

• Overachievers Alumni
Options

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

• ✭✭✭✭✭
Options

I did that, too, and still getting incorrect answer. Just baffled!

• ✭✭✭✭✭
Options

I want to know items in the upcoming 60 days is the desire!

• Overachievers Alumni
Options

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

• ✭✭✭✭✭
Options

AWWW! There we go! YAY! Thank you so much David!

• ✭✭✭✭✭
Options

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

• Overachievers Alumni
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Try removing the " around the 0.74%.

Did that work?

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

Thanks so much Andree! I just shared the sheet to you!

• Overachievers Alumni
Options

Also, remove the % symbol at the end of .74%. That will make it unparsable.

• ✭✭✭✭✭
Options

I noted that David and did remove, but still unparseable. It's crazy!

• Overachievers Alumni
Options

=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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!