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

1. ## 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

2. ## 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. ## 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

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

Hi. Hope you can find it helpful somehow..

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

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

6. ## 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

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

@excelteam, who do you refer to?

#### Thread Information

##### Users Browsing this Thread

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

#### 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