What is the simplest way to have data from a form be pulled monthly into reports/dashboard


I have created a smartsheet form to log information on service intakes. I have then created sheet summaries and reports with a bunch of information (age, demographic, reason for call, type of service requested etc.). From this I created a dashboard showing key metrics and graphs of my data. My challenge is now I would like to be able to pull data monthly rather than a total sum. How do I do this in the fewest steps? I want the data from the form to end up on a new dashboard each month.


  • SteyJ
    SteyJ ✭✭✭✭✭✭

    The way I do this is by creating a metric sheet.

    In the metric sheet, name your columns like this:

    [Key Metric] [JAN] [FEB] [MAR]...cont.

    For January

    =COUNTIFS({Key Metric}, "Key Metric Criteria", {Date}, IFERROR(MONTH(@cell), 0) = 1)

    For February

    =COUNTIFS({Key Metric}, "Key Metric Criteria", {Date}, IFERROR(MONTH(@cell), 0) = 2)

    For March

    =COUNTIFS({Key Metric}, "Key Metric Criteria", {Date}, IFERROR(MONTH(@cell), 0) = 3)

    and so on.

    Now, in a dashboard chart widget, you can use either the line or smooth line graph to map out the key metric using the data for each month.

    Hope this helps!


    Jacob Stey

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!