Checking 4 columns for values in Pivot Table

[dungyc]

I am using excel 2003.

What I am trying to achieve is to summarise the sales split between respective salepeople. We have an output in the format seen below in the raw data table, where the personal and costs vary significantly (ignoring the fact according to me we are selling the world most expensive fruit!).

What I currently have, is a Pivot table (not shown) that has Month as a Page Field, Sales Person #1 as a category field, summing the values of that (which is obviously missing the contribution from Sales #2, #3, & #4). The salesperson's initials may appear in any of the four columns.

Now I may be asking for something very simple, but I could not find anything pertinent from existing posts, is to be able to get a pivot table similar to the examples given for January & February respectively at the bottom of the example below.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Raw Data
2	Month	Item Sold	Sales Person #1	Sales Person #2	Sales Person #3	Sales Person #4	Item Cost	Sales Per Person Cost
3	January	Apple	EF	CD	AB	GH	1,200	300.00
4	January	Apple	CD				650	650.00
5	January	Banana	AB	EF			400	200.00
6	February	Cherry	AB	GH			800	400.00
7	February	Apple	AB				750	750.00
8
9	January Sales							February Sales
10		Sales Person	Product						Sales Person	Product
11			Apple	Banana	Cherry	Total				Apple	Banana	Cherry	Total
12	January	AB	300.00	200.00		500.00		February	AB	750.00		400.00	1,150.00
13	January	CD	950.00			950.00		February	CD				0.00
14	January	EF	300.00	200.00		500.00		February	EF				0.00
15	January	GH	300.00			300.00		February	GH			400.00	400.00
Sheet

I beleive the pivot table solution is most applicable, as the Personnel, and products can change month on month, and will be reflected by data updating, where multi condition sumif will require a lot more tweaking.

Let me know if it is not clear what I am trying to achieve.

 

[dungyc]

Is this possible? Or am I working towrds a fruitless goal? Any help would be appreciateD.

Thanks