+ Reply to Thread
Results 1 to 7 of 7

3-d conditional sum with both horizontal and vertical criteria

  1. #1
    Registered User
    Join Date
    06-06-2017
    Location
    NH, USA
    MS-Off Ver
    2013 64-bit
    Posts
    3

    Question 3-d conditional sum with both horizontal and vertical criteria

    Good afternoon,

    I have spent a few months trying to solve for trying to simplify a large amount of datasets that I do not maintain. These files are generated on a monthly basis, and the structure varies from month to month. They each have many tabs, with the month the data is for sorted in the columns, and the labels for each in the rows. These are updated each month, and the numbers change due to adjusted forecasts.

    My solution to work with these has been to bring the data into a joined file each month a new fileset is generated. I have a file where each fileset is represented in its own worksheet, and all data is in the same 2-d array for each sheet.

    In the columns to the left, I then "tag" each row of significance with various dimensions of what the data represents (region, person, etc). These columns are always in the same place, but the location of the tags varies month over month.

    Additionally, the row across the top has the month being forecasted for (all months from Jan-16 through Dec-17 in this case).

    What I am trying to do:
    I want to have a sheet that is able to look at each sheet of data, and total all of the cells that match both the vertical and horizontal criteria.

    Additional requirements:
    -The data has text in some of the cells. I never "need" the text, but some of my efforts have been thrown off due to this.
    -The data has blanks in some of the cells. Some of my attempts have been thrown by this.
    -In some cases, more than one cell will meet the criteria; this is expected.
    -In some cases, I may not want to populate every criteria (to get more "rolled up" totals.

    I realize this alone may be vague, though in reading examples, it can be hard to lay this out without a proper Excel file. I have created a simplified attachment that I believe covers the gamut of scenarios.

    Any thoughts?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: 3-d conditional sum with both horizontal and vertical criteria

    I used macro's to rearange the data.

    See if the result in the sheet output meets your question (criteria).
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: 3-d conditional sum with both horizontal and vertical criteria

    I converted the dates in D to strings to simplify my formulas a bit - otherwise, change each $D2 to TEXT($D2,"mmm-yy")
    Attached Files Attached Files
    Last edited by Bernie Deitrick; 06-06-2017 at 03:16 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Registered User
    Join Date
    06-06-2017
    Location
    NH, USA
    MS-Off Ver
    2013 64-bit
    Posts
    3

    Re: 3-d conditional sum with both horizontal and vertical criteria

    Thank you so much for the replies. I am still comparing them at this time. I will need to dig deeper to follow the logic on the macro side (since the data I provided was simplified compared to the actual datasets I will be working with, both from a size, and number of criteria standpoint).

    Bernie, I have played with your solution some, and it is great (and probably much less processor intensive than the sumproduct driven solutions I was trying).

    With an eye to volatility from an indirect/offset solution, I have tweaked it slightly to:

    =SUMIFS(
    OFFSET(INDIRECT("'" &$D2&"'!C"&(MATCH(DATEVALUE("Jan-17"),INDIRECT("'"&$D2&"'!1:1"))+1),FALSE),0,MATCH($E2,INDIRECT("'"&$D2&"'!1:1"),FALSE)-(MATCH(DATEVALUE("Jan-17"),INDIRECT("'"&$D2&"'!1:1"))+1)),
    INDIRECT("'" &$D2&"'!B:B"),$B2,
    INDIRECT("'" &$D2&"'!C:C"),$C2)

    The thought with the above is instead of starting at the fixed E:E and then backing out 5 from the match of the month, it now starts at the January value, regardless of column, and uses the match to offset from there.

    Seems to work swimmingly, though I will look to adapt it to my "real" model and make sure it all translates.

    That said, any thought to the last couple of cells with "blank" criteria? The hope is that, if a field is left blank, it will only use the criteria that is not blank (so, for example, if an agent name isn't specified, all agents will be included). I am playing with trying to use a combination of if() and isblank(), but I haven't gotten there yet.

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: 3-d conditional sum with both horizontal and vertical criteria

    I'm pretty sure you don't want to hard-code this:

    DATEVALUE("Jan-17")

    That should be a cell reference, too.

    If you want blank criteria to mean all, then you need to change =LongFormula to

    =IF(C2<>"",LongFormula,NewFormula)

    The NewFormula would be the same as the other, but without the last two parameters: INDIRECT("'" &$D2&"'!C:C"),$C2

  6. #6
    Registered User
    Join Date
    06-06-2017
    Location
    NH, USA
    MS-Off Ver
    2013 64-bit
    Posts
    3

    Re: 3-d conditional sum with both horizontal and vertical criteria

    I THINK I've got it with this:

    =SUMIFS(
    OFFSET(INDIRECT("'" &$D2&"'!C"&(MATCH(DATEVALUE("Jan-17"),INDIRECT("'"&$D2&"'!1:1"))+1),FALSE),0,MATCH($E2,INDIRECT("'"&$D2&"'!1:1"),FALSE)-(MATCH(DATEVALUE("Jan-17"),INDIRECT("'"&$D2&"'!1:1"))+1)),
    INDIRECT("'"&$D2&"'!B:B"),IF(ISBLANK($B2),"*",$B2),
    INDIRECT("'" &$D2&"'!C:C"),IF(ISBLANK($C2),"*",$C2))

    Neither the file month nor forecast month should be blank in my real-world usage, so I believe this will work.

    Regarding not hardcoding the date, I suppose I could assign it to a specific cell, I just know the "starting point" won't have variability in what I am doing (the first month would always be January in all datasets). But I may consider setting this on a hidden tab and pointing to it, in case a year from now I have a change of heart.

    Anything else I might be missing or overcomplicating?

    If not, this has been SO helpful, so thank you.
    Last edited by JudoFlash; 06-07-2017 at 10:07 AM.

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: 3-d conditional sum with both horizontal and vertical criteria

    Looks good - Do you need the IF(ISBLANK($B2),"*",$B2)?

    Just double-check that your returns are correct for a complicated test case, and you're done!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 10-03-2016, 03:39 AM
  2. Sum data with vertical and horizontal criteria
    By Vernell10 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-09-2016, 10:01 AM
  3. Sum with multiple criteria Horizontal and Vertical
    By freqzz in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2015, 04:39 AM
  4. Sum Multiple Criteria horizontal and vertical
    By baronk in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2015, 07:55 AM
  5. [SOLVED] 3 criteria for horizontal and vertical matching
    By mator in forum Excel General
    Replies: 5
    Last Post: 12-24-2014, 07:19 AM
  6. [SOLVED] Lookup with vertical and horizontal criteria
    By busygurl in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-23-2014, 12:03 AM
  7. Lookup Table Using Horizontal and Vertical Criteria
    By bbmonkey87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2014, 06:31 PM

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