Results 1 to 3 of 3

Multi-criteria sum with data from different tables

This is a discussion on Multi-criteria sum with data from different tables within the Excel Questions forums, part of the Question Forums category; Hello, I'm having a time figuring this problem out. My Setup: Sheet1, Table1 is a multiple column table. Column1 is ...

  1. #1
    New Member
    Join Date
    Nov 2006
    Posts
    11

    Default Multi-criteria sum with data from different tables

    Hello, I'm having a time figuring this problem out.

    My Setup:
    Sheet1, Table1 is a multiple column table. Column1 is employee name and column 2 is employee location. There are many more columns, but those are all that is important for this problem.

    Sheet2, Table2 is also a multiple column table. Column1 uses data validation to force the user to select an employee name from a drop-down. The employee name list is compiled using the data in Column1 of Table1. The rest of the columns contain data from vairous lookups, indexes, and other calculations.

    Sheet2, Table3 is also a multiple column table. The rows within this table contain various types of charges, for example facilities.

    My problem:
    In column2 of Table3, i need to sum all of the data in Column2 of Table2 and then multiply by a factor. However, i only need to sum the data for employees who are in certain locations. For example, if employee A and B (Table2, Column1) are both in location 1 (Table1, Column2), then sum the data in Table2, Column2 and multiply by X.

    What i've tried:
    Multiple formulas, incuding sumif, sum(if), and index, including combinations. I can get this to work simply if i include an additional column in Sheet2, Table2 that does a vlookup of Sheet1, Table1, Column2 and get's the location for the employee name selected in Sheet2, Table2, Column1. However, i don't want to do it this way. I only want Sheet2, Table2 to contain the selected employee name in Column1 and then the other columns to provide the calculations i already have present and working. I do not want to duplicate the location information from Table1. (Besides, that would be too simple!)

    Any help would be appreciated.

  2. #2
    Board Regular
    Join Date
    Mar 2006
    Posts
    1,710

    Default Re: Multi-criteria sum with data from different tables

    In table 3 column 2, type this formula:


    =SUM(IF(ISNUMBER(MATCH(Table2Column1,IF(Table1Column2=Location1,Table1Column1),0)),Table2Column2))

    Confirm with CTRL+SHIFT+ENTER

  3. #3
    New Member
    Join Date
    Nov 2006
    Posts
    11

    Default Re: Multi-criteria sum with data from different tables

    Worked perfectly. Thanks!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com