SUMIFS referencing 2 sheets with multiple criteria but all in 1 formula

SPRINGBOK

I've studied so many threads but still can't wrap my head around how to accomplish my goal. Background: I have 2 sheets that we use to track account openings here at the bank. Sheet 1 is active accounts that we are still waiting on signed ppwk for and once we rcv the paperwork back, those rows move to Sheet 2 which is structurally the same set up, we just move the rows off of the '1st' sheet to keep the clutter down for the main sheet admin who is opening accounts (that is, once the docs are received she doesn't need them on her sheet anymore). There is a roll up report that client support roles use that rolls up both sheets for the remainder of the onboarding process...yada yada... My goal is to have a 3rd "Formula Sheet" that will tally the following data from BOTH sheet 1 and 2 --> =SUMIFS([# of Accts]:[# of Accts], [Signing Method]:[Signing Method], "Paper", [Request Type]:[Request Type], "New Account", [Month Opened]:[Month Opened], "4")

I'm using the formula above to pull the data from Sheet 1 into the Sheet Summary on Sheet 1 and then I can do the same on Sheet 2 and add them together in the Sheet 2 Summary, but is there a way to just make a big long formula in a 3rd "Formula Sheet" that will essentially do this: =SUMIFS([# of Accts]:[# of Accts], [Signing Method]:[Signing Method], "Paper", [Request Type]:[Request Type], "New Account", [Month Opened]:[Month Opened], "4") (ALL FROM SHEET 1) +PLUS+ =SUMIFS([# of Accts]:[# of Accts], [Signing Method]:[Signing Method], "Paper", [Request Type]:[Request Type], "New Account", [Month Opened]:[Month Opened], "4") (ALL FROM SHEET 2) ?

This makes sense in my head but doesn't sound very clear here :( Thanks in advance! Happy to provide more info...

Shelley

SPRINGBOK

Thank you so much! This is very helpful! I appreciate it!

Mark Cronk

Hi,

You're on the right path. You'll use 2 SUMIFS formulas, one for each sheet, and add the results together. You'll need to use external ranges { } to your sheets. The basic syntax will be:

=SUMIFS({Sheet 1 [# of Accts]:[# of Accts]}, {sheet 1 [Signing Method]:[Signing Method]}, "Paper", {sheet 1 [Request Type]:[Request Type]}, "New Account", {sheet 1[Month Opened]:[Month Opened]}, "4") + SUMIFS({sheet 2 [# of Accts]:[# of Accts]}, {sheet 2 [Signing Method]:[Signing Method]}, "Paper", {sheet 2 [Request Type]:[Request Type]}, "New Account", {sheet 2 [Month Opened]:[Month Opened]}, "4")

Another way to do this is to enter each sheet's formula into a summary field on the sheet. Then create a summary report that pulls the 2 fields from the 2 sheets together.

Help?

Mark

SPRINGBOK

Thank you so much! This is very helpful! I appreciate it!

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark