I want to create a SUMIFS condition where I can refer to both arguments. I want to be able to say "IF the month and year of the first argument are equal to the month and year of the second argument." However, examples of SUMIFS that I've seen have a condition such as "=Food", where you can't actually perform any function on the other argument. How can this be accomplished?
SUMPRODUCT is more powerful than SUMIFS, although it is harder to understand.
=SUMPRODUCT( MONTH(B1:B20)=MONTH(D1), YEAR(B1:B20)=YEAR(D1), C1:C20 )
This checks all rows from 1 to 20 to see if the month of the date in column B is equal to the month of D1, and likewise for the year. If both are equal, then it sums the value from column C of that row.
Details on how SUMPRODUCT works:
If either the month or year is not equal, then the result of the first or second array for that row will be zero. This gets multiplied by whatever is in column C, resulting in zero. So the value that gets summed from that row is zero; in other words, it is ignored.