SUMIFS with ranges on multiple sheets

EagonA

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 😀

Carson Penticuff

In your "new" formula, you are referencing {Supplier AM} in both of your SUMIFS statements. Should one of these be something like {Supplier AM1}?

Carson Penticuff

In your "new" formula, you are referencing {Supplier AM} in both of your SUMIFS statements. Should one of these be something like {Supplier AM1}?

EagonA

Yes, that was exactly it! I needed to update that reference, as well. Simple! Thank you!