Trouble with SUMIFS formula across multiple sheets

Nannette Huber

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?

Nannette Huber

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!