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? Any help would be greatly appreciated. Thank you.

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

    Merge all the data into one contiguous table with a macro or Power Query and then use Pivot Tables or Formulas as required on that table. If you chose the formula route, the formulas you will need (if the data is in one table) will be a combination of some or or all of SUMIFS, INDEX, MATCH, SUMPRODUCT.

    There is a hire help forum here that you could pay someone to do it for you for stipend of your choosing.

    Ozgrid Hire Help


    Check out our new reputation system. Click on the Like button under the post!

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend[/SIZE]