Using VLookup, Displacement, SumIF to total and average

[Paul987]

Hello all -

I am trying to set up a spreadsheet that will track the performance o
several people selling several different products. Each day, it wil
track the number of a certain product sold, and the profit, for man
products. In the end, I would like to be able to analyze each salesma
by product, against the group, for a date range etc....

In column A I have the date
In column B I have profits product 1
In column C I have # sold Product 1
In column D I have profits product 2
In column E I have # sold Product 2

Trying to explain this the best I can...
On a seperate sheet"Overview", I have a list of all the products.
would like to be able to enter the tab name in a cell and specify
date range, and have the results for those products populate on thi
"overview" tab. I'm having a problem with the vlookup and th
conditional sum, formulas. Any help you could offer would be great.
will check back often to give details if necessary.
Thanks much,
Pau

 

[Dave Peterson]

First, I'd rearrange my data.

Column
A Date
B Salesman
C product
d Qtysold
e profit
F DateOk

Then I'd use a pivottable. But first I'd add one more column to the mix.

One that returned true/false if the date was ok.

=and(a2>=date(2005,11,1),a2<=date(2005,11,30))
and drag down.
(Give the header a nice "OkDate" title)

Then I'd select the range and data|Pivottable

Follow the wizard until you get to a dialog with "Layout" on it.
click that layout button
drag that okdate to the Page field
Drag the salesman to the row field
drag the product to the column field (more than 250'ish could cause trouble)
drag the numbers (sold/profits) to the data field.

and finish up.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Paul987]

Dave Peterson - you are the man.

I greatly under estimated the power of the mighty pivot table. It
worked out well, and the date column solved the date range problem.
Thanks for the help, you saved me hours of work.

Paul

 

[Dave Peterson]

Wow. That was fast. Glad you got it working.

Dave Peterson - you are the man.

I greatly under estimated the power of the mighty pivot table. It
worked out well, and the date column solved the date range problem.
Thanks for the help, you saved me hours of work.

Paul