SUMIFS When Criteria Takes Place in a Certain Month and Year
Hi Smartsheet Community,
I'm stuck on a formula where I'm trying to sum up how many occurrences happened each month and year for different items such as wage benefits, failed background check, etc (Please see screenshot below). I think I might be close. Does anyone have any ideas? Thank you!
=SUMIFS({Rescinded Reason}, HAS(@cell,KPI@row, {Candidate Loss Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2023))
Answers
-
Hey @Lea722
A SUMIFS requires the data to be summed is actually a number. 1234, etc. I think you are looking for countifs? If you are looking for a SUMIFS, the syntax is incorrect in the formula above.
=SUMIFS(range to be summed, range 1, criteria 1, range 2, criteria 2, etc)
=COUNTIFS({Rescinded Reason}, HAS(@cell,KPI@row, {Candidate Loss Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2023))
Will this work for you?
Kelly
-
Thank you for your quick response, @Kelly Moore! Duh, haha! In changing it to Countifs though, I'm getting a value of 0 next to each reason. I have data on the sheet it's pulling from. I tried removing the iferror and 0 too.
-
I missed the missing parenthesis in your HAS function
=COUNTIFS({Rescinded Reason}, HAS(@cell,KPI@row), {Candidate Loss Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2023)
Kelly
-
That did the trick! Thank you so much for taking the time to help, @Kelly Moore!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!