Complex Problem!!! MAYBE

  • Thread starter Thread starter maddog
  • Start date Start date
M

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????
 
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)
 
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.
 
=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)
 
Back
Top