Sumif or Index formula across multiple sheets

I've spent 2 hours trying to figure this out. My attached spreadsheet shows the formula results in green and the red lines are where I'm trying to create a formula that will read either the property number or name in column A&B and find any sheet that matches. For Example if the property is Red it would find all the sheets that have the same name of red in cell C3 of each sheet. Then it would look at the column heading in row 6 and would match the column head in each sheet and return the value that is in row 6 of each sheet that corisponds to the heading in row 6. If there are multiple tabs that have the same property name red it would add all the values up under that specific column heading.

Any help would be much appreciated.

Thanks