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

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:

 

 

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!