Monthly, 6 Monthly, 12 Monthly reporting

Garmon D

I'm currently working on a KPI structure for my organisation. An entry will be made each month via a Smartsheet form, and they'd like to know the latest entry, as well as the last 6 month and last 12 month totals. I'm struggling with how to calculate these. I assume that I'd use the max function along with a created date column for the monthly, although I haven't quite worked that one out either. Any thoughts much appreciated!

Kelsey Hayden

Hello,

Thanks for reaching out! It sounds like you are trying to track the dates when entries were made, and sum the totals if the entries were submitted within the last 6 months and last 12 months.

If so, then I first recommend including a Created Date system column in your sheet. This will automatically generate a timestamp when a form submission creates a new row on the sheet. In another Date type column, you can then locate the latest entry by utilizing the DATEONLY and MAX functions. Here is an example of what the formula would look like:

• =DATEONLY(MAX(Created:Created))

The DATEONLY function is used in a Date column to extract the date portion of a date/time value. In combination with the MAX function, the latest date portion in the range is pulled out.

In order to sum the monthly totals from the past 6 and 12 months, you can utilize the SUMIF function. The SUMIF function adds numbers within a range that meet a specified condition. In this case, we are specifying if the entry date falls within 6 or 12 months of the current date.

Here are some examples of what these formulas might look like in your sheet:

• =SUMIF([Date Column]:[Date Column], [Date Column]@row >= TODAY(-180), [Monthly Totals]:[Monthly Totals])
• =SUMIF([Date Column]:[Date Column], [Date Column]@row >= TODAY(-365), [Monthly Totals]:[Monthly Totals])

For more information on working with the SUMIF function, I recommend checking out this article from our Help Center: https://help.smartsheet.com/function/sumif

Please let me know if you have any questions about this and I’ll be more than happy to advise further!

Garmon D

Brilliant, I think that answers what I need - much appreciated!