Calculate YTD based on given criteria

Very new to Smartsheet:

I have a sheet with 14 columns. The first 12 are Jan-Dec. The 13th is the Reporting Month thru which I want smartsheet to calculate the YTD total. And the "PTD" column is the formula with the result.

Let's pretend we're in June, and I want to look back at Q1. I want to populate column 13 ("RPTMO" in image below) with the number 3 so smartsheet will add up Jan+Feb+Mar columns and put the result in the "PTD" column. In Excel I would use a helper row and then sumif, but without a helper row I don't know how to accomplish this without 12 embedded IF formulas, which is an ugly solution.

Any help would be appreciated!

Thank you!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

It would be the same in Smartsheet. Either a helper row or adding together 12 IF statements.

LadyHerbie

Thanks for the reply Paul.

My helper rows were always sitting above my column headers in Excel so they don't get confused with the data, and didn't get overwritten when we dropped in updated data. Is there some functionality in Smartsheet that allows for me to accomplish the same thing, other than setting aside row 1 (for example) for my helper formulas?

Thanks!

Paul Newcome

Unfortunately there is not.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.