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.

    Metric by Month

    ✭✭
    in Formulas and Functions

    Hello!

    Appreciate any formula help as always!

    Currently, we have several people fill out a form and on that form is an "Escalation date" which is just the day they are submitting the request.

    For example, in May, we had 20 escalations submitted and the actual sheet has those 20 rows with the escalation date column being 05/01, 05/05, 05/05, 05/07, etc etc.

    I want to be able to put on the dashboard a graph or metric showing that 20 escalations were input in May and then use the formula to do the same for January - September (and then future months). Any thoughts on what I could do?

    Thank you!

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Community Champion

      Hello @WesMcNally,

      My suggestion would be to create a buddy column to calculate the month for that entry (or month and year) and then you can either create a sum formula for metrics or a chart using the data.

      If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

      Monique Odom-Stearn

      Architecture Solutions Manager

      Smartsheet Leader & Community Champion

      Pronouns: She/Her (What’s this?)

      “Take chances, make mistakes, get messy!” – Ms. Frizzle

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      edited 09/20/23

      You can also do this without a helper column if you would like. You would use the COUNTIF formula with the MONTH function. It would look something like the formula below, where 5 is the number representing May.

      =COUNTIF([Escalation date]:[Escalation date], MONTH(@cell) = "5")

      Please let me know if that works for you!

      Edited to add: You could do these formulas for each month in a separate metric sheet or in the Sheet Summary. You could do a chart based on the metric sheet or a Sheet Summary report, or you could use the Sheet Summary field as a metric (if you don't want a chart).

      Amber Eakin, MSLS, M.Ed.

      Adult Education Specialist | Process Improvement Enthusiast

      · Share on FacebookShare on Twitter
    • ✭✭
      image.png
      image.png

      Hey @Amber Eakin . Thank you for the info. I can't seem to get it to work as I would like. I keep getting Invalid data type error. I referenced the sheet and highlighted the escalation date column. Anything I am doing wrong?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      @WesMcNally - Is that "Escalation Date" column an actual "Date" column type? If it's Text/Number, you'll get that "#INVALID DATA TYPE" error message.

      Amber Eakin, MSLS, M.Ed.

      Adult Education Specialist | Process Improvement Enthusiast

      · Share on FacebookShare on Twitter
    • ✭✭

      @Amber Eakin Yes, it's an actual Date column type.

      image.png


      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Oh good! Without actually seeing the reference, I'm not sure. The formula works for me, so the only thing I can figure is the reference.

      Amber Eakin, MSLS, M.Ed.

      Adult Education Specialist | Process Improvement Enthusiast

      · 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