Complex Sales Evaluation

[Rcan-sales]

I need to compute how much each sales person sells and their gross profit by
month. Unfortunatly the manager wants the information by ship date, not by
order date. I managed to find the total sales per month using this formula
and varying the dates to correspond with the month:

=(SUMIF($P2:$P990,">=01/01/2008",$Q2:$Q990))+(SUMIF($R2:$R990,">=01/01/2008",$S2:$S990))+(SUMIF($T2:$T990,">=01/01/2008",$U2:$U990))-(SUMIF($P2:$P990,">=02/01/2008",$Q2:$Q990))-(SUMIF($R2:$R990,">=02/01/2008",$S2:$S990))-(SUMIF($T2:$T990,">=02/01/2008",$U2:$U990))

Now I need help to figure out how to get the total profit - then the shipped
total and profit totals by sales person. Below is a subset of my data

C E I P
Q R
SO # Salesman % Profit Date ShippedA I.A.A. Date ShippedB S T
U
I.A.B. Date ShippedC I.A.C.
21574 CHUCK 18.98% 1/18/2008 $15,425.49 2/27/2008 ($576.87)
21681 CHUCK 34.35% 1/23/2008 $15,500.00 2/18/2008 $8,165.40
21718 LISA 35.64% 1/28/2008 $5,482.25 4/15/2008 $2,963.55
5/5/2008 $2,518.70
21744 CHUCK 29.66% 1/31/2008 $16,089.41 2/29/2008 $17,461.13
21760 CHUCK 30.81% 1/31/2008 $19,821.40 2/6/2008 ($1,206.83)
21764 CHUCK 27.51% 1/31/2008 $2,034.50 4/16/2008 ($2,034.50)
21870 CHUCK 30.88% 2/27/2008 $7,049.73 3/5/2008 $5,686.03
21873 CHUCK 30.00% 2/27/2008 $10,462.01 3/5/2008 $7,806.89
21928 CHUCK 43.53% 5/9/2008 $3,333.45 3/14/2008 $5,862.15
21997 CHUCK 41.38% 3/31/2008 $7,880.00 4/8/2008 $18,170.00
22052 LISA 33.60% 4/15/2008 $11,770.00 5/5/2008 $8,811.00
22081 LISA 38.07% 4/29/2008 $22,109.25 5/13/2008 $4,059.00
22097 CHUCK 41.91% 5/14/2008 $92,925.90 6/4/2008 $93,536.10
22109 CHUCK 35.82% 5/1/2008 $19,445.60 6/10/2008 $19,369.30
22128 CHUCK 28.71% 4/23/2008 $5,882.45 5/5/2008 $11,532.15
22143 CHUCK 28.89% 4/29/2008 $16,677.30 5/5/2008 $71,992.30
22149 CHUCK 46.17% 5/14/2008 $24,135.98 7/25/2008 $24,613.38
22182 CHUCK 37.64% 5/19/2008 $14,050.00 7/15/2008 ($9,375.00)
22214 LISA 29.15% 5/23/2008 $17,294.40 6/2/2008 $190,180.80
7/1/2008 $30,597.60
22230 CHUCK 61.23% 5/21/2008 $6,282.90 6/4/2008 $6,218.55
22245 CHUCK 37.00% 5/28/2008 $27,507.50 6/2/2008 $27,500.00


Sorry if this is unclear - also, we will be adding more sales people in the
future, so the formulas must be scaleable

Thanks for your help

rcan

 

[Roger Govier]

Hi

The easiest way (IMO) is to use a Pivot Table
For information on setting up Pivot Tables, take a look at Debra Dalgleish's
site
http://www.contextures.com/tiptech.html
scroll down to the section on Pivot Tables
or
Mike Alexander's video tutorials at
http://www.datapigtechnologies.com/ExcelMain.htm

If not, then use Sumproduct
On your Summary Sheet
Enter date for the Month required in cell B1 in the form 01 Jan 08
Enter Employee names in column A from A3 downward
Enter The Headings you want, in row 2, starting at B2

Create Dynamic named ranges for each of the columns of data on your source
sheet.
Insert>Name>Define>
Name Salesman
Refers to =Sheet1!$E$2:INDEX(Sheet1!$E:$E,COUNTA(Sheet1$E:$E)-1)

Repeat above procedure till you have defined names for each of the Headings
you use in Row 2 of your Summary
In cell B3 of your Summary enter

=SUMPRODUCT((Salesman=$A3)*(TEXT(ShipDate,"yymm")=TEXT($B$1,"yymm"))*(INDIRECT(B$2)))
Copy across and down as far as required.
Just change the Date in B1 to pick up data for any month required