# SUMIFS Month and Year

Options
✭✭✭✭

Hi, I have this formula to sum transactions that match the month - it works well.

=SUMIF({Bank Date}, MONTH(@cell) = 7, {Bank Received Amount}) - SUMIF({Bank Date}, MONTH(@cell) = 7, {Bank Expended Amount})

I want to add a second criterion to match the year as well, I get #INVLAID OPERATIONS.

=SUMIFS({Bank Date}, MONTH(@cell) = 7, YEAR(@cell) = Year@row, {Bank Received Amount}) - SUMIFS({Bank Date}, MONTH(@cell) = 7, YEAR(@cell) = Year@row, {Bank Expended Amount})

Tags:

• ✭✭✭✭✭✭
Options

Hi @Nancy Skoulphong

Hope you are fine, please try the following formula:

```=SUMIFS({Bank Date}, MONTH(@cell) = 7, @cell = Year@row, {Bank Received Amount}) -
SUMIFS({Bank Date}, MONTH(@cell) = 7, @cell = Year@row, {Bank Expended Amount})
```

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

=SUMIFS({Bank Date}, MONTH(@cell) = 7, @cell = Year@row, {Bank Received Amount}) - SUMIFS({Bank Date}, MONTH(@cell) = 7, @cell = Year@row, {Bank Expended Amount})

• ✭✭✭✭✭✭
Options

if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

My Email: Bassam.k@mobilproject.it

bassam.khalil2009@gmail.com

• Employee
Options

It looks like you're searching for two criteria in the same range, the Bank Date column, is that correct? If so, you'll need to list it twice: once for the MONTH, once again for the YEAR.

Try this:

=SUMIFS({Bank Date}, MONTH(@cell) = 7, {Bank Date}, YEAR(@cell) = Year@row, {Bank Received Amount}) - SUMIFS({Bank Date}, MONTH(@cell) = 7, {Bank Date}, YEAR(@cell) = Year@row, {Bank Expended Amount})

You may also be getting errors if the columns have blank data. Try wrapping an IFERROR statement around each MONTh and YEAR function, like so:

=SUMIFS({Bank Date}, IFERROR(MONTH(@cell), 0) = 7, {Bank Date}, IFERROR(YEAR(@cell), 0) = Year@row, {Bank Received Amount}) - SUMIFS({Bank Date}, IFERROR(MONTH(@cell), 0) = 7, {Bank Date}, IFERROR(YEAR(@cell), 0) = Year@row, {Bank Expended Amount})

One final thing to check is all the columns you're referencing. If there's an error in your Bank Date column in the other sheet, this will automatically cause the final formula referencing it to error as well. You'll need to fix the initial source of the error in the referenced column, does that make sense?

Let me know if this works for you!

Cheers,

Genevieve