dcbaaa1960

New Member
Joined
Sep 18, 2014
Messages
1
Hello,

I have a database like this

DateCustomerItem 1Quantity 1Item 2Quantity 2Item 3Quantity 3
1-Jan-14Aorange5apple8banana3
5-Jan-14Bapple3
5-Jan-14Cbanana4apple2
5-Feb-14Aapple6
8-Feb-14Abanana4orange2
8-Feb-14Cbanana7

<tbody>
</tbody>

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

ABCTotal
AppleBananaOrangeAppleBananaOrangeAppleBananaOrangeAppleBananaOrange
1-Jan-14835000000835
5-Jan-148353002401375
5-Feb-1414353002401975
8-Feb-141477300211019187

<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
ABCTotal Sum of Quantity 1Total Sum of Quantity 2
applebananaorangeapplebanana
(blank)orangeapple(blank)apple(blank)
Row LabelsSum of Quantity 1Sum of Quantity 2Sum of Quantity 1Sum of Quantity 2Sum of Quantity 1Sum of Quantity 2Sum of Quantity 1Sum of Quantity 2Sum of Quantity 1Sum of Quantity 2Sum of Quantity 1Sum of Quantity 2
1-Jan-1400005800000058
5-Jan-140000583042001210
5-Feb-146000583042001810
8-Feb-146042583042702912
Grand Total

<tbody>
</tbody>

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

Regards,
Puzzled excel user
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
DateCustomerItem 1Quantity 1Item 2Quantity 2Item 3Quantity 3
01-Jan-14Aorange5apple8banana3
05-Jan-14Bapple3
05-Jan-14Cbanana4apple2
05-Feb-14Aapple6
08-Feb-14Abanana4orange2
08-Feb-14Cbanana7
CUSTOMER ACUSTOMER ACUSTOMER ACUSTOMER BCUSTOMER BCUSTOMER BCUSTOMER CCUSTOMER CCUSTOMER C
orangeapplebananaorangeapplebananaorangeapplebanana
>>>>>>71470300211
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

Board Regular
Joined
Apr 11, 2010
Messages
11,003
CUSTOMER ACUSTOMER ACUSTOMER ACUSTOMER BCUSTOMER BCUSTOMER BCUSTOMER CCUSTOMER CCUSTOMER C
orangeapplebananaorangeapplebananaorangeapplebanana
01/01/201407/01/2014583030024
08/01/201414/01/2014000000000
15/01/201421/01/2014000000000
22/01/201428/01/2014000000000
29/01/201404/02/2014000000000
05/02/201411/02/2014264000007
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,290
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top