Complex Problem!!! MAYBE

[maddog]

Here is my dilema.

I need to work out staff commission on a sale by sale basis. Easy. What
i then want to do is for the sheet to automattically for each week
calculate what each salesperson gets, number of sales etc.

Data Entered


Date Invoice# Customer Sale_Amount SalesPerson CostPrice

Spreadsheet works out;

Profit %GP Commission



Then for each week I need total sales for each salesperson, total
commission.


I have been working on how to do this, but it would involve writting 52
formulas for each salesperson etc etc.

What is the best way,

Database????

 

[Bob Phillips]

List the salesman in A1:A52 on another sheet.

Then in B1, something like

=SUMPRODUCT(--(Sheet1!$E$2:$E$2000=$A1),Sheet1!$D$2:$D$2000-Sheet1!$F$2:$F$2
000)

and copy down.

I don't know the rule for %GP or commission

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[maddog]

Awesome that gets me the total sales for a salesperson. Thanks so much,

now how do i restrict that to between two dates.


Your assistance is greatly appreciated.

 

[Bob Phillips]

=SUMPRODUCT(--(Sheet1!$E$2:$E$2000=$A1),--(Sheet1!$A$2:$A$2000>=--"2006-04-0
1")
--(Sheet1!$A$2:$A$2000<=--"2006-04-30"),Sheet1!$D$2:$D$2000-Sheet1!$F$2:$F$2
000)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)