Help with a Rolling 12 Month Formula

Hello, thank you for taking the time to look at this and I appreciate any assistance anyone out there might be able to supply.

I'm trying create a Rolling 12 Month Formula and running into an issue getting it to work. I have multiple programs on a sheet that have metrics and are nested under a blue header row. The data owner wants the sheet laid out like in the screenshots where the Column Names are "Month Year" (this info is being moved from an Excel file so they're trying to keep the visual look as much as possible). However, I know I cannot reference the Column Names in formulas. So I put corresponding dates (MM/01/YEAR) in the header rows for each program and put the text in the same color as the cell (you can see the dates slightly in the screenshots since I have the rows selected). My thought was I could then have the formula reference those cells for the applicable dates. So in the "Rolling 12" column, I just need a formula to Sum up the values in the "Volume" row if they fall within 12 months of the current day. However, any examples I've found on here are not working. Is it because I don't have a true Date column the formula can reference from (since the values are running across in a row)? Or do I need to add more columns for a full 12 months (currently only have data starting in September 2022, but I could just add all of 2022 in put in 0 if needed)? Again appreciate any help or assistance.

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Jeff Reisman

@Kevin_A

You're going to be much better off using a regular grid to hold your data in two columns, or you can put the dates and values in hidden columns on the "pretty" sheet, and then reference those values and lay them out horizontally. .

Set up a DataDate (date type) column and a DataValue (text/number) column. Add in your dates and values. At the bottom of your DataValue column, create a SUMIFS formula to count everything from the past 1 year:

 =SUMIFS(DataValue:DataValue, DataDate:DataDate, >= TODAY(-365))

Now, in your "pretty" horizontal columns for the business owner, reference these cells and then the result of the SUMIFS in the Rolling 12 column:

Kevin_A

@Jeff Reisman thanks for the reply and suggestion. It's a good solution to keep the "pretty look", but it won't quite work for my needs. I have 4 other programs that are totaling up into the overall "All Programs" section, so I think it would get unruly trying to go this route 4 times over with the hidden columns for 4 programs one sheet. You did give me an idea though; I could just separate all the programs out into their own sheets and use your method to keep the "pretty look" for them. Then I could just create a report to pull them all together and wouldn't have to worry about the doing hidden columns on there at least.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/360317#Comment_360317

Using the separate sheets is a great idea. 👍️

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.