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

  • Paul McGuinness
    Paul McGuinness Overachievers

    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

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Your date criteria would look something like this:

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!