Countif the Month/Year matches the current Month/Year?

Current formula displayed at bottom. It counts the amount of Values in Range 1 that are created in the last 30 days. The formula works, however I'd like to change the second countifs statement, in particular the Today(-30) so that it matches the month/year of the date. That way, it will track the Values in Range 1 created that's creation month matches the current month of the year. {Range 2} is a "Created" Date column. I also have 2 columns created, Month and Year, that extract the Month and Year from that "Created" column. I'm just not sure how to translate that into the countifs formula.

Current formula is =COUNTIFS({Range 1}, Values@row, {Range 2}, >TODAY(-30))

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Kyle Cannella

    We can use the MONTH Function and YEAR Function along with the TODAY Function to see if these two criteria match!

    Ex:

    MONTH(@cell) = MONTH(TODAY())

    However sometimes the first part will error if there are blank cells in the range or cells with text. To ignore these, we'll wrap that in an IFERROR:

    IFERROR(MONTH(@cell), 0) = MONTH(TODAY())

    Try this full formula:

    =COUNTIFS({Range 1}, Values@row, {Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Range 2}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!