SUMIFS from 2 sheets

ginamt3

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

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.

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.

G.

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.

Genevieve P.

Wonderful! I'm glad I could help 🙂