# How do I count how many cells are last month but not considered expired?

Options
edited 02/21/20

I am working on a sheet for currency of qualifications for our employees. I have the equations that will tell me how many employees are a month before they are due, how many are due this month, and how many are in the past but now I have to find and equation that will let me count how many qualification are only one month past due.

I have tried reversing my expires next month formula but it counts all past due dates not just the one month past.

Equation I tried : =COUNTIFS(MEDICAL:MEDICAL, <=TODAY(), MEDICAL:MEDICAL, <TODAY(30))

Also the way I am able to count expired qualifications was by creating a checkbox column next to the qualification column and using equation

=IF(MEDIAL1 < TODAY(), 1)

and then in the summary sheet using

=COUNTIF([medical expired]:[medical expired], 1)

So how can I change those to exclude one month past due.

for example if it is Feb I want to be able to count how many were due in Jan only, then separately count how many were Dec and beyond in the past.

• ✭✭✭✭✭✭
Options

Try changing your second criteria to greater than thirty days in the past (and leaving the less than today portion).

=COUNTIFS(MEDICAL:MEDICAL, <=TODAY(), MEDICAL:MEDICAL, >TODAY(-30))

• ✭✭✭✭✭✭
Options

Try changing your second criteria to greater than thirty days in the past (and leaving the less than today portion).

=COUNTIFS(MEDICAL:MEDICAL, <=TODAY(), MEDICAL:MEDICAL, >TODAY(-30))

• Options

Hi Paul,

Thank you for such a quick response I tried your formula and it seems to be working!!!!

Thank you!!

• ✭✭✭✭✭✭
Options

Excellent. Happy to help! 👍️

Please don't forget to mark the most appropriate response as "helpful". That way others searching for a similar solution can know that one may be found here.

• Employee
Options

The new Healthcare Community Group is now live! This group is a space for all things Healthcare and your question sounds like a great use of a discussion post in the group. I encourage you to ask questions, share use cases and collaborate with others in your industry. We also have some exciting healthcare-related updates being announced in the Healthcare Community Group very shortly.

Please let me know if you have any questions about the new group!

-Brianne

Brianne Laird, Sponsorships & Brand Activations Program Manager

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!