COUNTIFS within a Date Range
Hello,
I have a column of dates that are formatted as dates. I am trying to use COUNTIFS to tally the number of instances in which a date from the column is less than 1 month from today, between 1 and 3 months from today, between 3 and 6 months away, and between 6 and 12 or more months away.
For less than one month away, I used =COUNTIFS({CMS Range 8}, <=(TODAY() + 30), {CMS Range 7}, =true) which correctly returns 3.
For between 1 and 3 months away I am trying =COUNTIFS({CMS Range 8}, <=(TODAY() + 90), >(TODAY() + 30), {CMS Range 7}, =true) but it gives an #INVALID OPERATION error. What am I missing?
Thank you,
--Dean.
Answers
-
Try using an AND statetment.
=COUNTIFS({CMS Range 8}, AND(@cell <=(TODAY() + 90), @cell >(TODAY() + 30)), {CMS Range 7}, =true)
-
I forgot to include the range for my second criteria:
=COUNTIFS({CMS Range 8}, <=(TODAY() + 90), {CMS Range 8}, >(TODAY() + 30), {CMS Range 7}, =true)
-
@Dean Arvan Yes. You can do that as well.
I personally prefer the AND statement because it keeps things a little more organized in my opinion and keeps you from having to repeat the range multiple times (especially if you have a lot of different criteria for the same range).
I haven't tested if it affects sheet performance one way or the other.
Help Article Resources
Categories
Check out the Formula Handbook template!