Help with Sales Totals

[SMonczka]

Help with Sales Totals

I have a sales log for all our sales people, about 25 people. In it
we list by row the Date, the Sales Rep, the Part Number and the Sale
Amount. This is a running log that starts at the first of the year and
is closed out at the end of the year.

For example:

The sales log says

Date Rep Part $
4/5 Steve B 10.00
4/5 Brian B 15.00
4/5 Steve A 5.00
4/5 Steve A 10.00
4/6 Brian B 15.00

I can not change this log or add anything to it.

Each day, on a separate sheet I have to log the Total Amount for each
Sales Rep by Part. What I would like to be able to do is have a
second sheet where I would put in the Date, the Reps Name and the Part
number and have the sheet calculate the total for me.

4/6 Steve B ?
4/6 Steve A ?


Is this at all possible?

Thanks,
Steve M.

 

[DDM]

Steve, if you could make a tiny modification in the sales log, your task
would be very easy. The modification would be to assign a dynamic name to
the sales log data, using the instructions you'll find here (under "Use A
Dynamic Data Source"): http://www.contextures.com/xlPivot01.html.

Then create a pivot table: Data > Pivot Table > Data Source = the dynamic
name you created. Layout: Page field = Date; Row fields = Rep, Part; Data
field = $. Put it on a new worksheet.

The pivot table will give you, day by day, the total $ for each Rep for each
Part. Just select a date from the page field.

All you would have to do is refresh the Pivot Table after each day's data
entry in the sales log.

 

[Harry Bo]

Another alternative to Pivots is this
=SUMPRODUCT(--(A2:A6=A12),--($B$2:$B$6=B12),--($C$2:$C$6=C12),($D$2:$D$6))

Col A = Dates
Col B = Rep
Col C = Part
Col D = Sales value
Enter data in these cells
A12 = Date to lookup
B12 = Reps name
C12 = Part (A or B)
D12 enter the formula above

You will also need to rename the arrays to the correct sheet:
=SUMPRODUCT(--(Sheet1!A2:A6=A12),--( etc

Harry

 

[DDM]

Harry and Steve, SUMPRODUCT is a great alternative to the pivot table
approach. Calculating daily totals for 25 sales reps by X number of products
could entail creating a lot of formulas, however. So I would recommend
creating a matrix along the following lines:

In A1: Date to lookup
In B1, C1, etc.: Reps' Names
In A2, A3, etc.: Product Names

In B2, enter the following formula...

=SUMPRODUCT(--(Sheet1!$A$2:$A$65000=$A$1),--(Sheet1!$B$2:$B$65000=B$1),--(Sh
eet1!$C$2:$C$65000=$A2),(Sheet1!$D$2:$D$65000))

....and copy down and to the right. Then, day-by-day, you can copy this sheet
and change the date.

 

[SMonczka]

I just wanted to thank both of your for your help. It make take some
time for me to decipher all of this, but I will. Thanks again.

Steve