Suggestions for calculating the total number of entries per month, YoY

Options

I am looking for a way to chart the total number of entries on a sheet by month, year over year. (based on the 'submission date' field)

I have created a column with a formula to return the "month created". Can I expand this to include the year, and then total and summarize this data?

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/03/23
    Options


    Within the cell that has month created you can use + to add the year created as well. This works like & in excel. You probably want a space between the two.

    This is how it looks when used to join the text in two cells:

    =[Column2]@row + " " + [Column3]@row

    You would replace the cell references (shown in bold) with your formula.

    =[Column2]@row + " " + [Column3]@row

    So your formula would look like this:

    = your_formula_for_month + " " + your_formula_for year


    Alternatively, you could create a separate column to return "year created" and then set up your summary in another sheet to be one row per month and one column per year, which would enable you to do a year to year comparison.

  • davidsmi
    davidsmi ✭✭
    edited 11/13/23
    Options

    Thanks, KPH. I've figured out the formula to return the month/year created. I now want to summarize all of this data and pull it into a report.

    I could create new summary fields then pull these into a report, but creating a new summary field for every single month of every single year seems a bit inefficient, so wondering if there is a better way to do this.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!