COUNTIF Date in the next 30 days without counting blanks

Emily McNeeley

I am using the following formula: =COUNTIF([Contract Expiration Date]:[Contract Expiration Date], to count how many are due in the next 30 days. The formula is returning 34 results but if I filter on the same criteria of date in the next 30 days, I only get 5. Is my formula counting blanks? If so, how do I correct to not count blanks?

Emily McNeeley

my formula got cut off above -

=COUNTIF([Contract Expiration Date]:[Contract Expiration Date], >TODAY(+30))

Purnima Gore

Hi @Emily McNeeley

It is not counting blanks.

=COUNTIF([Contract Expiration Date]:[Contract Expiration Date], <TODAY(30))

The formula is almost correct. What you are asking the formula to do in your example is to count the number of days that are greater than 30 days from now.

By changing the operator to less than < you are asking the calculation to give you expiration dates less than 30 days from today.

Hope that helps.

Purnima

Emily McNeeley

Thanks @Purnima Gore - when I flip it, it gives me 12, which is still more than the filter. So here is what I have in my Sheet Summary: =COUNTIF([Contract Expiration Date]:[Contract Expiration Date], <TODAY(+30))

And here is my filter

My sheet summary shows 12 but my filter shows 5

Purnima Gore

Hi @Emily McNeeley

Can you give me access to the sheet?

I was able to have a list of 43 contracts with 5 of them within 30 days and the rest over that showing a different number.

This is with the normal filter

This is with the formula - still

Still only counting 5.

Thanks

PG