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

Debbie Petersen

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!

Andrée Starå

@David Tutwiler

Yes, those are so easy to miss.

Debbie Petersen

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?

David Tutwiler

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

Debbie Petersen

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

Debbie Petersen

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

David Tutwiler

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

Debbie Petersen

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

Debbie Petersen

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

David Tutwiler

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

Debbie Petersen

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

Andrée Starå

@dskpetersen

Try removing the " around the 0.74%.

Did that work?

Debbie Petersen

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

Andrée Starå

@dskpetersen

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)

Debbie Petersen

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

David Tutwiler

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

Debbie Petersen

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

David Tutwiler

=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?