Monthly, 6 Monthly, 12 Monthly reporting

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!

Find more posts tagged with

Formulas

Comments

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!

Screen Shot 2018-10-16 at 4.29.56 PM.png

Screen Shot 2018-10-16 at 4.30.13 PM.png

Garmon D

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…