Search code examples
excelsumifs

Microsoft Excel 2016: Using SUMIFS with multiple criteria: same month and same year

I have an Excel sheet of financial transactions. It has a "Transaction Date" column and an "Amount" column. I want to create a new column, to show the sum of monthly amounts. For that, I'm writing an "IF" condition, such that if the "MONTH" of the current cell is different from the "MONTH" of the preceding cell, then add up all the "Amount" of which has the same "YEAR" and "MONTH" as my current cell. Otherwise, return blank. Seems pretty straightforward.

I'm writing the following formula, where E2 is in the "Transaction Date" column, and E3 is the preceding input in the "Transaction Date" column:

=IF(NOT(EXACT(MONTH(E2),MONTH(E3))),SUMIFS([@Amount],[@[Transaction Date]],MONTH(E2),[@[Transaction Date]],YEAR(E2)),"")

which apparently doesn't work. I also try the following formula, which returns error:

=IF(NOT(EXACT(MONTH(E2),MONTH(E3))),SUMIFS([@Amount],MONTH([@[Transaction Date]]),MONTH(E2),YEAR([@[Transaction Date]]),YEAR(E2)),"")

Thanks for the help.

Solution

  • you cannot modify the ranges inside the SUMIFS() so bracket the dates:

    SUMIFS([Amount],[Transaction Date],">=" & DATE(YEAR(E2),MONTH(E2),1),[Transaction Date],"<" & DATE(YEAR(E2),MONTH(E2)+1,1))

    You IF also can be simplified to MONTH(E2)<>MONTH(E3)

    So:

    =IF(MONTH(E2)<>MONTH(E3),SUMIFS([Amount],[Transaction Date],">=" & DATE(YEAR(E2),MONTH(E2),1),[Transaction Date],"<" & DATE(YEAR(E2),MONTH(E2)+1,1)),"")