Rolling 12 Month Count

Hello -

I have a sheet that is capturing data via a form. The date of capture is included on the sheet.

I am also capturing the data for 4 different locations on the same sheet . . A, B, C, D.

I would like summary sheet which counts the entries per month for the last 12 months for each location. I would like the data to update based on the current date without having to change formulas every month. Is this possible?

Find more posts tagged with

Accepted answers

All comments

APell

I do this a lot. One of my tricks is to start at the bottom and work backwards and up. To clarify, make row 12 the current month. So you need a date column, then a month column. The date row 12 would be =Today(). Month column at row 12 would be =month(date@row). Then month row 11 is =IF((Month12 - 1) < 1, 12, Month12 - 1) then drag that all the way up. So now you have a constantly revolving list of months with the bottom row always this month. The bottom to top is important for reports and charts. So, I can select the bottom 6 rows for a chart to display the trends for T6M without having to change anything and the current month is on the right and oldest month on the left in a line graph or similar.

So now you can make as many columns as you want to summarize things from the form sheet. You can also have 4 rows per month, so you can summarize for each location.

If you never want to open this sheet, another trick is to make a master sheet to push the Today() function. I have a sheet that, each morning at 4am, records a date, which of course is today. Because it is a workflow, I can link any sheet to that date instead of using =Today() and the date will update each morning at 4am.

Neil Watson

@tsammons adding to the above, I also use a similar approach although I find that working in columns suits me better.

I create a helper metric sheet, the rows and columns in light blue are for explanation, the rows in darker blue contain the formulas for the rolling 12 months (in various formats). Each row in the source sheet should have appropriate helper columns to identify the month, year, etc.

The formula's in the lower rows with sumifs or countifs reference the source sheet and the appropriate month, year, etc. on the metric sheet. The lower table can then be used for charts in dashboards.

Hope this helps.

tsammons

@Neil Watson thanks for this . . quick question . . in the month_number formula, how do you deal with negative numbers? For example, in the "This month -9" column, my formula returns "-1" instead of 11.

Neil Watson

@tsammons I made a mistake in the screenshot I shared - each cell has a separate formula as per the example below.

Hope this is clear now.

gbrown

https://community.smartsheet.com/discussion/comment/340519#Comment_340519

@APell Great stuff for the month values! But how do you calculate the Year value for each of the rolling 12 months?

APell

https://community.smartsheet.com/discussion/comment/367678#Comment_367678

You can just make a year column and do the same thing with a formula, but use years instead. The bottom one is year(date@row), then the one above is =IF(month@row = 1, [year]12 + 1, [year]12)

That gets you a year that adds one each time the month changes to 1, which happens each time the prior month is 12. So that is a constantly revolving list of months and years with 'today' being the very bottom row.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.