Rolling 12 Months CountIFs Statement

Archie123456

I have 3 different dates that I all want counted into a rolling 12 months

Date Set #1 - =COUNTIF({Date Budget #1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

Date Set #2 - =COUNTIF({Date Budget #2}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

Date Set #3 - =COUNTIF({Date Budget #3}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

I want this to all be in one line for the three sets of dates count if they are within January in 2024.

Find more posts tagged with

Accepted answers

All comments

Paul McGuinness

Hi @Archie123456

You could SUM the separate formulas to combine their individual results

=SUM(COUNTIF({Date Budget #1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024)),COUNTIF({Date Budget #2}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024)),COUNTIF({Date Budget #3}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024)))

Hope that helps.

Paul

Archie123456

Is there a way to instead of making it a hard 12 months can i change it to a rolling 12 for example

Jan 24

Feb 24

Mar 24

Apr 23

May 23

Jun 23

July 23

Aug 23

So on and so on. So when we move into Apr 24 it gets rid of the 23 Sumed numbers and will calculate the Year 2024 numbers. If this makes sense

Paul Newcome

Your date criteria would look something like this:

=COUNTIF({Date Budget #1}, @cell>= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())))

Archie123456

Hi Paul,

This did not seem to work when i put the formula in it was showing

Jan 24 - 392

The right number of Date Budget is 35 for Jan 24.

Paul Newcome

Are you able to provide some screenshots for context?

Archie123456

I want it to automatically update per month on a rolling 12 month basis the bigger number was what your formula did.

=COUNTIF({Date Budget #1}, @cell>= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())))

This is where i want it to display

Paul Newcome

Ah. I misunderstood. Try this:

=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = IF(TODAY()>= DATE(YEAR(TODAY()), 1, 1), YEAR(TODAY()), YEAR(TODAY()) - 1))

The two bold/italic 1s above would be what you change to indicate the different months. You can use cell references for them as well to make the formula a little more dynamic.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.