+ Reply to Thread
Results 1 to 4 of 4

Sumproduct with 2 criteria

  1. #1
    Registered User
    Join Date
    06-14-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2016
    Posts
    57

    Sumproduct with 2 criteria

    Hi all,

    I have a bunch of information which I need to extract specific information per employee.

    There are only 5 different salary sacrific companies at the moment (potential to change like the employees who are participating.)
    I have tried a sumproduct and sumifs formulas but can't get either of them to work currently.

    1st tab is the table of employees, their sal sac company and work code.
    Second tab is the detail information - I have deleted most of the rows and columns and just left the sal sac columns (so the column numbers change all the time, same as the row numbers).
    To make things a little more complex most of guys work on multiple projects in 1 month - however their sal sac alwasy goes to one of the numbers shown.

    What formula will work so that I can extra the value of their sal sac based on sal sac company (B), employee code (A) and employee (C).

    Ta.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-14-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2016
    Posts
    57

    Re: Sumproduct with 2 criteria

    =SUMPRODUCT(SUMIFS(INDEX('Month Detail Report'!1:1048576,0,MATCH('Salary Sacrifice'!B58:D58,'Month Detail Report'!2:2,0)),'Month Detail Report'!A:A,'Salary Sacrifice'!A58,'Month Detail Report'!B:B,'Salary Sacrifice'!E58))

    Í've use the above formula - but I currently get a #Value! result.

  3. #3
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Sumproduct with 2 criteria

    Hi

    Try this

    cell F2 =SUMPRODUCT(SUMIFS(INDEX('Month Detail Report'!$1:$1048576,0,MATCH(B2,'Month Detail Report'!$2:$2,0)),'Month Detail Report'!A:A,A2,'Month Detail Report'!B:B,E2)) then copy down
    Last edited by micope21; 05-11-2012 at 10:56 AM.
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  4. #4
    Registered User
    Join Date
    06-14-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2016
    Posts
    57

    Re: Sumproduct with 2 criteria

    Thanks for that, just that small change made a huge difference.

    Ta.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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