Count per month based off created date

Hello,

I'm struggling to get the count of submissions per month. For the submission date I used the "created date" in the drop down options for this specific column. I am not getting any errors with my formula it's simply counting them as "0." For example, June should show a total of 4 submission, but my formula is returning 0. My questions is if you can use the COUNTIF function with this type of column in smartsheets?

My current formula is as follows: =COUNTIFS(Submitted1:Submitted6, "MONTH(6)")

However, I have tried various formulas with the same output.



Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @kgerholdt ,

    One way is to use a "helper" column. Setup another column titled something like "Submitted Month" and use the following formula: =MONTH([Submitted]@row)

    Then in another field you can use =COUNTIF([Submitted Month]:[Submitted Month], =6)

    You can do this for each month you want a count of.

    Hope this helps.

    Dave

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @kgerholdt ,

    One way is to use a "helper" column. Setup another column titled something like "Submitted Month" and use the following formula: =MONTH([Submitted]@row)

    Then in another field you can use =COUNTIF([Submitted Month]:[Submitted Month], =6)

    You can do this for each month you want a count of.

    Hope this helps.

    Dave

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    edited 08/15/23

    @kgerholdt The MONTH() function returns the numerical month when you included the date in the parentheses of the function. Also, functions never go in quotes. The way your formula is written Smartsheet is looking for the literal text value of "Month(6)" and that is why you are getting a count of 0 since this text value doesn't exist anywhere in the range.

    Your formula should be: =COUNTIFS(Submitted1:Submitted6, MONTH(@cell) = 6)

    Or if you want it to be over the entire Submitted range even for new entries, =COUNTIFS(Submitted:Submitted, MONTH(@cell) = 6)

    The @cell operated allows the MONTH() function to evaluate the range row by row.

  • kgerholdt
    kgerholdt ✭✭✭

    Thank you, both!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!