SUMIFS cross sheets for months and years

I am trying to calculate total Amount in sheet#1 below by referring to sheet#2's [Date Sent to Client] and [Related Opportunity: Amount]:

Sheet#2:

So the Amount in sheet#1

SUMIFS({Sheet#2}[Related Opportunity: Amount], YEAR(Sheet#2[Date Sent to Client]=STC-year), MONTH(Sheet#2[Date Sent to Client]=STC-Month)

I have tried many times but still frustrated to make it work. Thanks

Find more posts tagged with

Sheets

Accepted answers

Paul Newcome

You are going to need something that looks like this...

=SUMIFS({Sheet 2 Amount Column}, {Sheet 2 Date Column}, AND(IFERROR(YEAR(@cell), 0) = [STC-Year]@row, IFERROR(MONTH(@cell, 0) = [STC-Month]@row))

All comments

Paul Newcome

You are going to need something that looks like this...

=SUMIFS({Sheet 2 Amount Column}, {Sheet 2 Date Column}, AND(IFERROR(YEAR(@cell), 0) = [STC-Year]@row, IFERROR(MONTH(@cell, 0) = [STC-Month]@row))

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.