Formula Within Next 30, 60, 90 days

Andrea Mayer

Hello -

Looking for help with a basic formula that will calculate how many projects end within a certain number of days.

I have used: =COUNTIF({End Date}, <=TODAY(30))

I am not receiving an error message, but the result is much higher than the number is should be.

Any help you can provide is appreciated!

-Andrea

Find more posts tagged with

Hospitality

Formulas

Project Management

Comments

Paul Newcome

That is because using only <= TODAY(30) means you are also including all dates in the past as well. You need to build in the condition of also being >= TODAY().

=COUNTIFS({End Date}, AND(@cell <= TODAY(30), @cell >=TODAY()))

Andrea Mayer

Hi Paul -

Thanks for your help with this. Would this formula work if I am using this formula to reference the data from another sheet? When I enter this I am still receiving a number higher than what it should be (now receiving 35 and it should be 4).

-Andrea

Paul Newcome

Yes. It will work for cross sheet referencing.

As for the incorrect number...

Can you post a screenshot of your range, so we can take a look at things?

Andrea Mayer

Sure - is this what you are looking for:

EndDate Range.PNG

Andrea Mayer

Ah, I was missing a parameter in my formula. So, the formula was pulling the correct number for what I was asking it to do. I have it all figured out now.

Thank you!

Andrea

Paul Newcome

Excellent! Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…