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.

Find more posts tagged with

Formulas

Accepted answers

All comments

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 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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.