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))

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

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

Lea722

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.

Kelly Moore

@Lea722

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

Lea722

That did the trick! Thank you so much for taking the time to help, @Kelly Moore!

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.