COUNTIFS within a Date Range

Dean Arvan

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.

Paul Newcome

Try using an AND statetment.

=COUNTIFS({CMS Range 8},AND(@cell<=(TODAY() + 90),@cell >(TODAY() + 30)), {CMS Range 7}, =true)

Dean Arvan

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)

Paul Newcome

@Dean ArvanYes. 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.