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.
Best Answer
-
The criteria for the date range would be:
AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 3)
Answers
-
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"
-
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.
-
Try something like this:
=SUMIFS({Number Of Actions}, {Region}, @cell = 4, {Dates}, IFERROR(YEAR(@cell), 0) = 2022)
-
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.
-
Can you provide a screenshot of the source data as well as the exact formula you are using?
-
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)
-
And which columns are each of your ranges covering?
-
Sorry sir. Range 11 = Number to be Reviewed, Range 6 = Assigned Service Team, and Range 2 = Date Assigned to NQT
-
Try changing the second criteria to match what you actually have in your source data.
"Region 1" "Region 3" etc.
-
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.
-
The criteria for the date range would be:
AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 3)
-
Sir it worked perfectly!!! Thank you so much for all of your help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!