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

  • Kelly Moore
    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
    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
    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
    Lea722 ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!