Results 1 to 4 of 4

Dynamic sum across various sheets - sumifs / sumproduct / indirect

Threaded View

sktcrvr Dynamic sum across various... 07-01-2021, 05:08 AM
Glenn Kennedy Re: Dynamic sum across... 07-01-2021, 07:36 AM
sktcrvr Re: Dynamic sum across... 07-01-2021, 08:31 PM
Glenn Kennedy Re: Dynamic sum across... 07-02-2021, 03:06 AM
  1. #1
    Registered User
    Join Date
    07-01-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    2

    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.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SumIFS with dynamic sheet reference not using INDIRECT
    By MichaelLuthi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-19-2019, 11:55 PM
  2. Multisheet summary with Sumproduct, Sumifs, Indirect, and from to range
    By Chrisbou in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-03-2019, 02:15 PM
  3. SUMPRODUCT/SUMIFS Dynamic array doesn't works
    By regoroes in forum Excel General
    Replies: 5
    Last Post: 01-04-2018, 03:01 AM
  4. Replies: 12
    Last Post: 05-19-2016, 11:08 PM
  5. Optimize SUMPRODUCT with dynamic sum range. SUMIFS??
    By ron2k_1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2015, 05:33 PM
  6. [SOLVED] SUMPRODUCT(SUMIF(INDIRECT with Dynamic Worksheet Range
    By decipher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2014, 05:40 AM
  7. Replies: 3
    Last Post: 06-19-2014, 10:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1