Sum previous month data across year end

I'm trying to write a formula to summarize warranty dollars for "treaters" in December to present on a dashboard. Below is what I have for a formula but it is saying "#invalid operation" and I haven't been able to figure out what I'm doing wrong.

=SUMIFS(Treater:Treater, Year:Year, IF(Month:Month = 1, =(YEAR(TODAY()) - 1), =YEAR(TODAY())), Month:Month, =(MONTH(TODAY()) - 1))

Here's what I was using that worked until we crossed into the new year: =SUMIFS(Treater:Treater, Year:Year, =YEAR(TODAY()), (Month:Month), =(MONTH(TODAY()) - 1))

Any suggestions would be greatly appreciated! Thanks!

Find more posts tagged with

Accepted answers

Paul Newcome

The problem is with the MONTH function. Today's month number is 1. 1 - 1 = 0 which is not a valid month number.

You would want to use the same idea that you did for the year with the IF statement and say that if the current month number is 1, then output 12, otherwise do the current month number minus 1.

All comments

Paul Newcome

The problem is with the MONTH function. Today's month number is 1. 1 - 1 = 0 which is not a valid month number.

You would want to use the same idea that you did for the year with the IF statement and say that if the current month number is 1, then output 12, otherwise do the current month number minus 1.

Lynn Strahm

Got it, Thanks Paul! I also had a couple extra "=" in there that were causing problems. Here's the formula I ended with:

=SUMIFS(Treater:Treater, Year:Year, IF(MONTH(TODAY()) = 1, (YEAR(TODAY()) - 1), YEAR(TODAY())), Month:Month, IF(MONTH(TODAY()) = 1, 12, (MONTH(TODAY()) - 1)))

Paul Newcome

Happy to help. 👍️

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.