SUMIFS Month and Year

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})

Thank you for your help.


Tags:

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    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})
    

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Nancy Skoulphong
    Nancy Skoulphong ✭✭✭✭

    @Bassam Khalil Thank you for your speedy reply. I tried and received the #INVALID OPERATION error.

    =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 Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Nancy Skoulphong

     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: [email protected]

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Nancy Skoulphong

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!