Count if Expiration Date is in the next 3, 14, 30 Days without counting Past Expiration Dates
My Formula is "=COUNTIF({Expiration Date}, <TODAY(3))" switching out the number for the appropriate timeframe.
When I do a filter on the sheet it is referencing to there is only 1 expiring in the next 3 days however the formula keeps giving me an answer of 2.
I am unsure if it is counting the 2 that are past the expiration date or what.
Best Answer

The easiest way would be to create a helper column for the days remaining until the expiration date and reference that column in the function.
=COUNTIFS({Expiration Date}, <TODAY(3),{Days Remaining}, >0)
Answers

Hello @Ava Blessie
Have you tried less than or equal to <=TODAY(3)?
If you're only using <, the function won't include day 3, only everything less than that.

@MichaelTCA I did have that in my original Formula I must have not had it copied over; its still counting the ones that are past the expiration date.

Got it. You will need to use the COUNTIFS function, with a S at the end. Add another criteria that requires the difference between today and the expiration date to be greater than 0. I like to use the NETDAYS function.
If the difference is less than 0, it's technically "in the past".

Oh thank you!! So how would you rewrite that formula?

The easiest way would be to create a helper column for the days remaining until the expiration date and reference that column in the function.
=COUNTIFS({Expiration Date}, <TODAY(3),{Days Remaining}, >0)


@Ava Blessie Of course! Anytime.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!