SUMIFS with ranges on multiple sheets

Options

Hi there!

I have a metrics sheet that I use to calculate the number or weeks each account manager booked on a given week of the year. The formula I have currently that works references one sheet only. However, that source sheet has an automation set up so that once the booking is completed, the row moves to an archived sheet. Once it does that, the number of weeks booked on the metrics sheet will no longer reflect those weeks. So my thought is to add on to the original formula in the metric sheet to also check the archived sheet as a second range. Can I combine the 2 ranges in the existing formula, or will I need to do a SUMIFS + SUMIFS? When I have tried the SUMIFS + SUMIFS, it has not worked for me.

Here's the metric sheet:

Here's the current formula referencing the 1 sheet:

=SUMIFS({New Booking Weeks}, {Supplier AM}, $[Supplier AM]@row, {Date Booked}, IFERROR(WEEKNUMBER(@cell), 0) = [06-Jan]$1, {Date Booked}, IFERROR(YEAR(@cell), 0) = [06-Jan]$2)

Here's my attempt at the SUMIFS + SUMIFS that isn't working:

=SUMIFS({New Booking Weeks}, {Supplier AM}, $[Supplier AM]@row, {Date Booked}, IFERROR(WEEKNUMBER(@cell), 0) = [06-Jan]$1, {Date Booked}, IFERROR(YEAR(@cell), 0) = [06-Jan]$2) + SUMIFS({Archived Weeks Booked}, {Supplier AM}, $[Supplier AM]@row, {Date Booked1}, IFERROR(WEEKNUMBER(@cell), 0) = [06-Jan]$1, {Date Booked1}, IFERROR(YEAR(@cell), 0) = [06-Jan]$2)

Thanks!

@Paul Newcome, the formula guru 😀

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!