Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    How to count date occurrences within a month

    ✭✭
    in Formulas and Functions

    I have a sheet that identifies the date of new survey submissions and the date of responses. I am looking to count the dates that occur within specific months/years for a report... Here is what I am using, which is currently giving me a response of 0, though I know there are multiple occurrences for September.

    The ranges are in date columns.

    =COUNTIFS({*MASTER TABLE* Range 4}, "MONTH(@cell)" = 9)

    I have also tried =COUNTIFS({*MASTER TABLE* Range 4}, "MONTH(@cell)" = 09)


    Any Ideas?

    Tags:
    · Share on FacebookShare on Twitter

    Best Answer

    • Community Champion
      Answer ✓

      Hi @Merritt R,

      The criteria should not be in the quotation marks as this will mean the COUNTIFS is looking for that as text, rather than as a function.

      However, checking this you'll get an error. You can get round this easily though:

      =COUNTIFS({*MASTER TABLE* Range 4},<>"",{*MASTER TABLE* Range 4}, MONTH(@cell) = 9)

      Since your relevant values will have both a month of 9 (for September) and not be blank.

      Hope this helps, but any problems/questions just ask! 🙂

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      Hi @Merritt R,

      The criteria should not be in the quotation marks as this will mean the COUNTIFS is looking for that as text, rather than as a function.

      However, checking this you'll get an error. You can get round this easily though:

      =COUNTIFS({*MASTER TABLE* Range 4},<>"",{*MASTER TABLE* Range 4}, MONTH(@cell) = 9)

      Since your relevant values will have both a month of 9 (for September) and not be blank.

      Hope this helps, but any problems/questions just ask! 🙂

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions