Help with YEAR TODAY()) in SUMIFS Formula

Hi everyone. I am hoping you can assist me. I am trying to SUMIF the number of assignments we received in the current year, by month and by the service team. My formula is:

=SUMIFS({WORKSHEET COLUMN WITH RANGE}, {WORKSHEET COLUMN WITH RANGE}, =[NAME OF REGION]@row, {COLUMN WITH MONTHS DISPLAYING AS JAN, FEB, MAR, ETC}, =Dec$94)

Everything works great by the month but I want to add in to on sum if current year. I keep getting an Unparseable error when I add the year at the end.

Find more posts tagged with

Accepted answers

Paul Newcome

The criteria for the date range would be:

AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 3)

All comments

Sameer Karkhanis

Firstly, you do not need to add = sign when trying to match the criteria. Secondly, what is $ sign in the place you are comparing month? If you trying to match Dec 94 then it should be in quotes like "Dec 94"

Peppey

Sir,

Thank you so much for your assistance. I apologize because I did a poor job explaining what I need. In the first explanation, I was trying to use a helper that showed just the months. I figured that would not be helpful :).

I have snipped below a version of my sheet that I need to add sum. I am trying to add up the Number of Actions Received, By Region, and Based on the Month and Year. In this example, I would only want to sum up the 2022 actions so Region 4 would have a total of 377 actions (Sum up dates 3/31/2022 and 6/12/2022). Any help would be appreciated.

Paul Newcome

Try something like this:

=SUMIFS({Number Of Actions}, {Region}, @cell = 4, {Dates}, IFERROR(YEAR(@cell), 0) = 2022)

Peppey

Sir,

Thank you so much for your help. I tried the above formula but its not showing up the correct amount. For example, in March we completed 232 actions for Region 1. When I put the formula in it shows 0 for March.

Paul Newcome

Can you provide a screenshot of the source data as well as the exact formula you are using?

Peppey

Yes sir. Below is the snip for the source data

The formula is below (formula is on another sheet) to try and calculate number to be reviewed, by date and by region

=SUMIFS({FS HRMSS Staffing_Class_Quals_Inventory_T Range 11}, {FS HRMSS Staffing_Class_Quals_Inventory_Tr Range 6}, @cell = 1, {CY22 FS HRMSS Staffing_Class_Quals_Invento Range 2}, IFERROR(YEAR(@cell), 0) = 2022)

Paul Newcome

And which columns are each of your ranges covering?

Peppey

Sorry sir. Range 11 = Number to be Reviewed, Range 6 = Assigned Service Team, and Range 2 = Date Assigned to NQT

Paul Newcome

Try changing the second criteria to match what you actually have in your source data.

"Region 1" "Region 3" etc.

Peppey

Sir, this worked great for the year total. How can I break it down to get a total just for March? I tried entering the month in there but it did not work. So sorry for the troubles.

Paul Newcome

The criteria for the date range would be:

AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 3)

Peppey

Sir it worked perfectly!!! Thank you so much for all of your help.

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.