SUMIFS from 2 sheets

I use the formula below to add up work amounts on a weekly basis. rows get moved to another sheet once they are shipped. To keep history I need it to check from both sheets.

=SUMIFS({Master JOBV Range 1}, {MASTER SCHEDULE Range 1}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {MASTER DATABASE Range 1}, "Ed Bowen")

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @ginamt3

If you're referencing two different sheets, you will need to add together two separate formulas (you can't reference two sheets within the same function).

E.g:

=SUMIFS(sheet 1 formula) + SUMIFS(sheet 2 formula)

So if this is your formula for the one sheet, re-create the same formula and add it to the end:

=SUMIFS({Master JOBV Range 1}, {MASTER SCHEDULE Range 1}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {MASTER DATABASE Range 1}, "Ed Bowen") + SUMIFS({Sheet 2 Master JOBV Range 1}, {Sheet 2 MASTER SCHEDULE Range 1}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Sheet 2 MASTER DATABASE Range 1}, "Ed Bowen")

Let me know if this makes sense!

Cheers,

Genevieve

ginamt3

worked like a charm! THANK YOU.

All comments

Gareth Jeanes

Hi @ginamt3 ,

Are you having issues with the formula not working correctly?

If so could you send a screen shot of your formula within smartsheet to assist in identifying the issue?

Cheers.

Genevieve P.

Hi @ginamt3

If you're referencing two different sheets, you will need to add together two separate formulas (you can't reference two sheets within the same function).

E.g:

=SUMIFS(sheet 1 formula) + SUMIFS(sheet 2 formula)

So if this is your formula for the one sheet, re-create the same formula and add it to the end:

Let me know if this makes sense!

Cheers,

Genevieve

ginamt3

worked like a charm! THANK YOU.

Genevieve P.

Wonderful! I'm glad I could help 🙂

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.