Dynamic sum across various sheets - sumifs / sumproduct / indirect

Issue: Require a SUMIFS formula to sum a common cell reference in a range of sheets, if that sheet has a certain characteristic.

Context: I have built a financial model to analyse property development of several "precincts" that contain individual buildings ("elements") within the precincts. Best way to think about it is your local large box retail park as being one precinct and the buildings within are the elements. My cashflows contain items such as rental income, construction costs, professional fees, etc.

My goal is to accurately sum each cashflow item on a monthly basis to summarise the cashflows at a precinct level. Critically, i need the flexibility to change the precinct in which i attribute the elements to (using a switch in the element control panel).

I have setup two control panel tabs one for 4 overarching precincts and another for 10 individual elements that each are characterized by the precinct they are in. e.g.
Elements 1 to 3 are in precinct 1, elements 4 to 6 are precinct 2, elements 7 to 7 are precinct 3, and elements 8 to 10 are precinct 4.

Ideally, i would like to flick the switch on the element control tab and change its precinct and have that reflect directly in the precinct level cashflow.

Current approach:

I have tried several methods to which i have not had any success. These include using the sumproduct and indirect functions (see the attached spreadsheet).

Request: I need a formula that i can copy across and down in the precinct tabs that captures all cashflows of that precinct type as specified in the individual elements.


I have attached a very simplified version of my model that hopefully helps clear up any misunderstanding. Please ask if clarification is needed.

Thanks in advance.

Thanks Glenn, this is very helpful.

I too considered the approach of using only 1 sheet for the precinct but unfortunately i need to show the performance metrics of each precinct simultaneously (think IRR, NPV, money multiples, etc.).

Will revert if I have any difficulties.