SUMIFS from 2 sheets

Options

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

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    ginamt3 ✭✭✭✭
    Answer ✓
    Options

    worked like a charm! THANK YOU.

Answers

  • Gareth Jeanes
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    ginamt3 ✭✭✭✭
    Answer ✓
    Options

    worked like a charm! THANK YOU.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Wonderful! I'm glad I could help 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!