SUMIFS - Contains value in cell from another sheet

Hi All, 

I want to create a "Total Vendor Paid Amount" column on our "Main Dashboard". This column will need a formula that pulls information from another dashboard called "REO Vendor Invoicing (3)". I want the formula to sum the total invoices paid, in the "REO Vendor Invoicing (3)" dashboard, of jobs that contain a cell value, found on our "Main Dashboard". The cell value will have a partial address and will need to search the "REO Vendor Invoicing (3)" Dashboard for any cell that contains that value. We will then only need to sum the rows that have the "Status" in "New Invoice/Review Required", "Approved", or "Paid/Processed". 

This is such a complicated formula and I've been at it for days, but I just can't seem to crack the code. Is there anyone out there who can offer some help? 

Here's what I have so far:

=SUMIFS({REO Vendor Invoicing (3) Range 3}, {REO Vendor Invoicing (3) Range 1}, "*"Address1"*", {REO Vendor Invoicing (3) Range 4}, OR(@cell = "New Invoice/Review Required", @cell = "Approved", @cell = "Paid/Processed"))