Rolling 12 Months CountIFs Statement
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.
Answers
-
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
-
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
-
Your date criteria would look something like this:
=COUNTIF({Date Budget #1}, @cell>= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())))
-
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.
-
Are you able to provide some screenshots for context?
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!