SUMIFS When Criteria Takes Place in a Certain Month and Year

Lea722

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

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!