Sum Calculation including a date range

I need to figure out a formula that looks at column ranges 23-Jan to 27-Jan and sums the totals highlighted in blue, if the date range is within a rolling 12 years from the current date.

This is the Excel formula that was used (which was more columns):

=sumif($DF$4:$GZ$4,">"&Today()-365,DF7:GZ7)

I can't figure it out in Smartsheet based on the screenshot below.

Find more posts tagged with

Accepted answers

DKazatsky2

Hi @Tonya Briere,

The syntax for the DATE() is "DATE(year, month, day)". In the first part of your formula, you can see these values present and separated by commas. Your second use, you only have the year portion - if you add in values for month and day you should get past the error.

All comments

zealvert

@Tonya Briere

The appearance of your screenshot suggests that you've renamed the column headers as dates, which, in Smartsheet, is merely a reference to a column.

Whatever be the column names, you need to place the dates under a column whose type is Date (and just in case if you pull the dates through any formula / function, then please keep the column option Restrict to dates only disabled).

I've placed below a screenshot from my end to make it more understandable to you (I've colored the date rows, that qualify your criteria, as green).

And the Smartsheet formula (appears below) that sums your desired numbers, has been inserted under the column titled Total (in the cell showing 4.25):

=SUMIFS(Values:Values, Dates:Dates, >=DATE(YEAR(TODAY()) - 11, 1, 23), Dates:Dates, <=DATE(YEAR(TODAY()), 1, 27))

Please note that the above formula shows "-11" because the current year is being considered as the 12th year)

SCREENSHOT

Hope this solves your problem!

Best

Tonya Briere

@zealvert - This formula doesn't appear to be working as I'm receiving #UNPARSEABLE . I substituted the Values:Values with my horizontal range, so I'm guessing that's messing things up??

zealvert

@Tonya Briere

The sum range in your SUMIFS() function (i.e., the very first argument, 23-Jan:27-Mar]@row) is basically an array, which is not supported by the SUMIFS() function. You will need to keep all the values under a single column (and not multiple columns).

This is the only issue I can see in here.

(PS: You can observe the same setup in my screenshot and formula).

Paul Newcome

@Tonya Briere That particular error is coming from your first range. Any column name that has a space, special character, and/or number in it has to be wrapped in square brackets.

=SUMIFS([23-Jan]@row:[27-Mar]@row, ………..

Tonya Briere

@zealvert - unfortunately having my sum values in a column is not conducive to my needs for the spreadsheet. I may end up keeping the doc in Excel, which is not a big deal.

Tonya Briere

@Paul Newcome - Paul, thanks for the note on the brackets, unfortunately it still didn't work for my formula that I need as I'm still receiving an #UNPARSEABLE error.

May just keep my doc in Excel as I know it works there and I don't have any other sheets supplying data to this document.

DKazatsky2

Hi @Tonya Briere,

You are still getting #UNPARSEABLE because your second DATE() function is missing the Month and Day portions.

Tonya Briere

@DKazatsky2 - I apologize, but can you help me with how that portion of the formula should read (to include Month & Day)? I've tried several ways and I'm still getting an error.

Greatly appreciate your help.

DKazatsky2

Hi @Tonya Briere,

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.