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.

Find more posts tagged with

Formulas

Accepted answers

All comments

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

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

@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: Bassam.k@mobilproject.it

Genevieve P.

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.

https://community.smartsheet.com/discussion/comment/299789#Comment_299789

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

Nancy Skoulphong

https://community.smartsheet.com/discussion/comment/299790#Comment_299790

@Bassam Khalil Thank you. I was able to make it work.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.