Trouble with SUMIFS formula across multiple sheets

Nannette Huber
Nannette Huber ✭✭✭
edited 07/20/21 in Formulas and Functions

Hi Geniuses -

I am trying to summarize a sheet ("All Staff Reports") on another sheet ("Monthly Comparisons"). In the Monthly Comparison sheet, I want to sum a column (ERTS - how many) from the All Staff Reports sheet according to the Month and Year columns (both also on the All Staff Reports Sheet).

I've tried a number of different iterations of a formula, most often getting unparsable results. My current attempt is:

=SUMIFS({All Staff Reports Range 1}, IFERROR({All Staff Reports Range 2}, 0) = "July", IFERROR({All Staff Reports Range 3}, 0) = 2021)

All Staff Reports Range 1 = ERTS - how many column

All Staff Reports Range 2 = Month column

All Staff Reports Range 3 = Year column

which at least attempts to calculate but comes back #INVALID OPERATION. I cannot figure out where to go from here. Can you help?

Best Answer

  • Nannette Huber
    Nannette Huber ✭✭✭
    edited 07/20/21 Answer ✓

    A little more work got me to the right formula!

    =SUMIFS({All Staff Reports Range 1}, {All Staff Reports Range 4}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021))

    with All Staff Reports Range 4 being the Date column

    If anyone has a more elegant way to do this, please feel free to respond!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!