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:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,201
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top