Pivot table question

[dcbaaa1960]

Hello,

I have a database like this

Date	Customer	Item 1	Quantity 1	Item 2	Quantity 2	Item 3	Quantity 3
1-Jan-14	A	orange	5	apple	8	banana	3
5-Jan-14	B	apple	3
5-Jan-14	C	banana	4	apple	2
5-Feb-14	A	apple	6
8-Feb-14	A	banana	4	orange	2
8-Feb-14	C	banana	7

<tbody>
</tbody>

and would like to use it to create a summary table (using pivot table?) like the below

	A			B			C			Total
	Apple	Banana	Orange	Apple	Banana	Orange	Apple	Banana	Orange	Apple	Banana	Orange
1-Jan-14	8	3	5	0	0	0	0	0	0	8	3	5
5-Jan-14	8	3	5	3	0	0	2	4	0	13	7	5
5-Feb-14	14	3	5	3	0	0	2	4	0	19	7	5
8-Feb-14	14	7	7	3	0	0	2	11	0	19	18	7

<tbody>
</tbody>

The summary table should show the running total of orders by customer (A, B, C, etc.) and then by type (orange, apple, etc.). It should also show the running total of order by all customers by type.

There are a few constraints:
1) I cannot change the format of the database.
2) New rows (and potentially columns) will be added to the database over time.
3) I would like the summary table to adjust itself as the database changes. At the moment, I have defined the range of the database using an OFFSET function and create a pivot table that refers to the defined range. The reason I am using the pivot table function is because it is the only way I know to make sure that the table would refresh itself with the database. Please let me know if there are better ways.

I am facing these problems:
1) I cannot get Excel to aggregate the data under the columns "Item 1", "Item 2", etc. and reorganise it into a summary grouped by type (Apple, Orange, etc.) In other words, ideally Excel should sum the numbers under the columns "Quantity 1" and "Quantity 2" if the corresponding entries under "Item 1" and "Item 2" are the same (let's say they are both "Apple"). Instead, the pivot table would add the Apples under "Item 1"/"Quantity 1" separately from the Apples under "Item 2"/"Quantity 2".
2) I cannot get Excel to split the total order from all customers by "Apple", "Orange", etc. I can get the total number of fruit or total number of Quantity 1, but not a total number of Apples.

The below is what my pivot table currently looks like, which is very different from what I want.

	Column Labels
	A						B		C				Total Sum of Quantity 1	Total Sum of Quantity 2
	apple		banana		orange		apple		banana
	(blank)		orange		apple		(blank)		apple		(blank)
Row Labels	Sum of Quantity 1	Sum of Quantity 2	Sum of Quantity 1	Sum of Quantity 2	Sum of Quantity 1	Sum of Quantity 2	Sum of Quantity 1	Sum of Quantity 2	Sum of Quantity 1	Sum of Quantity 2	Sum of Quantity 1	Sum of Quantity 2
1-Jan-14	0	0	0	0	5	8	0	0	0	0	0	0	5	8
5-Jan-14	0	0	0	0	5	8	3	0	4	2	0	0	12	10
5-Feb-14	6	0	0	0	5	8	3	0	4	2	0	0	18	10
8-Feb-14	6	0	4	2	5	8	3	0	4	2	7	0	29	12
Grand Total

<tbody>
</tbody>

Any advice on how to solve this is much appreciated. Thank you very much.

Regards,
Puzzled excel user

 

[oldbrewer]

Date

Customer

Item 1

Quantity 1

Item 2

Quantity 2

Item 3

Quantity 3

01-Jan-14

A

orange

5

apple

8

banana

3

05-Jan-14

B

apple

3

05-Jan-14

C

banana

4

apple

2

05-Feb-14

A

apple

6

08-Feb-14

A

banana

4

orange

2

08-Feb-14

C

banana

7

CUSTOMER A

CUSTOMER A

CUSTOMER A

CUSTOMER B

CUSTOMER B

CUSTOMER B

CUSTOMER C

CUSTOMER C

CUSTOMER C

orange

apple

banana

orange

apple

banana

orange

apple

banana

>>>>>>

7

14

7

0

3

0

0

2

11

formula in cell marked >>>>

=SUMPRODUCT(($B$2:$B$7=RIGHT(D$15))*($C$2:$C$7=D$16)*($D$2:$D$7))+SUMPRODUCT(($B$2:$B$7=RIGHT(D$15))*($E$2:$E$7=D$16)*($F$2:$F$7))+SUMPRODUCT(($B$2:$B$7=RIGHT(D$15))*($G$2:$G$7=D$16)*($H$2:$H$7))

this is a start

<colgroup><col span="3"><col><col><col><col><col><col><col><col><col><col span="8"></colgroup><tbody>
</tbody>

 

[oldbrewer]

			CUSTOMER A	CUSTOMER A	CUSTOMER A	CUSTOMER B	CUSTOMER B	CUSTOMER B	CUSTOMER C	CUSTOMER C	CUSTOMER C
			orange	apple	banana	orange	apple	banana	orange	apple	banana
	01/01/2014	07/01/2014	5	8	3	0	3	0	0	2	4
	08/01/2014	14/01/2014	0	0	0	0	0	0	0	0	0
	15/01/2014	21/01/2014	0	0	0	0	0	0	0	0	0
	22/01/2014	28/01/2014	0	0	0	0	0	0	0	0	0
	29/01/2014	04/02/2014	0	0	0	0	0	0	0	0	0
	05/02/2014	11/02/2014	2	6	4	0	0	0	0	0	7
		now we can sort by dates also, formula in cell returning 5 in top left of new table
		=SUMPRODUCT(($A$2:$A$7>=$B35)*($A$2:$A$7<=$C35)*($B$2:$B$7=RIGHT(D$33))*($C$2:$C$7=D$34)*($D$2:$D$7))+SUMPRODUCT(($A$2:$A$7>=$B35)*($A$2:$A$7<=$C35)
		*($B$2:$B$7=RIGHT(D$33))*($E$2:$E$7=D$34)*($F$2:$F$7))+SUMPRODUCT(($A$2:$A$7>=$B35)*($A$2:$A$7<=$C35)*($B$2:$B$7=RIGHT(D$33))*($G$2:$G$7=D$34)*($H$2:$H$7))

<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>