+ Reply to Thread
Results 1 to 7 of 7

Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #

  1. #1
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #

    Please see attached worksheet

    Having some problems figuring this out. Basically when I enter an emp ID, Commission amount and date on one sheet, I want it to calculate the total commissions to the correct employee based on the month on sheet2. Please help.

    Sheet1
    B2:B21 = Employee ID number - 1, 2, 3 or 4
    C2:C21 = Commission $ amount varied
    D2:D21 = Date in the following format 12/28, etc.

    Sheet2
    A2:A13 = Months January – December
    B2:B13 = formula to calculate commissions for employee 1 based on corresponding month
    C2:C13 = formula to calculate commissions for employee 2 based on corresponding month
    D2:D13 = formula to calculate commissions for employee 3 based on corresponding month
    E2:E13 = formula to calculate commissions for employee 4 based on corresponding month
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #

    Example.xlsx See sheet 3.

    Why not just use a pivot table and refresh it when data changes?

    There may be a way to auto update when changes are made but I'm not sure how that works.

  3. #3
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #

    =SUMIFS(Sheet1!$C$2:$C$21,Sheet1!$B$2:$B$21,Sheet2!B$1,Sheet1!$D$2:$D$21,">="&$A2,Sheet1!$D$2:$D$21,"<="&EOMONTH($A2,0))
    But change your MOnth to Dates(1/1/13,1/2/13...) And format as mmmm
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  4. #4
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    642

    Re: Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #

    Hi. Hope you can find it helpful somehow..
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Re: Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #

    Im not following. Can you explain the last part please.

  6. #6
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #

    Try this:

    =SUMPRODUCT((ID=COLUMNS($B:B))*(TEXT(Date,"mmmm")=$A2)*Commission)

    See the attached file
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    642

    Re: Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #

    @excelteam, who do you refer to?

+ 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. [SOLVED] Need formula to calculate commissions for employees based on the month earned on 2 sheets
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2013, 12:09 AM
  2. [SOLVED] Formula needed for auto fill across multiple worksheets-see attached spreadsheet
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2013, 10:38 PM
  3. Help needed - IF Statements, trying to calculate commissions
    By nosca in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2013, 08:44 PM
  4. Replies: 1
    Last Post: 03-31-2011, 05:03 PM
  5. [SOLVED] Help With Formula To Calculate Commissions
    By Smonczka in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-04-2005, 11:50 AM

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