SUMIF Previous Month, Current Year

Hello,

I am trying to use the Sheet Summary feature to calculate [Hours worked] for the previous month based on a date column [Service End Date]. I have the following formula which is giving me a much larger value than expected and I believe this is due to having multiple years of data and only looking at the month it is pulling data from the previous month of several years. I have not been able to figure out how to add a criteria for current year.

=SUMIF([Service End Date]:[Service End Date], MONTH(@cell) = MONTH(TODAY() - 1), [Hours worked]:[Hours worked])

I have read several other posts and solutions but am not having any luck. Thanks in advance!

Find more posts tagged with

Accepted answers

Paul Newcome

Try this...

=SUMIFS([Hours worked]:[Hours worked], [Service End Date]:[Service End Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))

All comments

Paul Newcome

Try this...

James Caison

That worked perfectly Paul! I appreciate your time.

Paul Newcome

Happy to help. 👍️

James Caison

I modified this formula to look forward a few months a but once we get into the next year (2022) the result is 0. I've tried several ways but get the same 0 result. What am I missing. Thanks in advance

=SUMIFS([Man Days]:[Man Days], [Start Date]:[Start Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) + 2), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))) * 8

Nate H

Hey, @Paul Newcome, is there a way to add another provision to your formula here? I'm wanting to only sum the values from last month where "Northeast" is in the column labeled "Region." I've tried adding another AND formula but my syntax must be off.

=SUMIFS([Referred Out]:[Referred Out], [Date Only]:[Date Only], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()), AND(Region = "Northeast"))))

Paul Newcome

@Nate H You would need to continue with the appropriate syntax of range comma criteria.

Nate H

Thanks, sir. @Paul Newcome

Paul Newcome

@Nate H 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.