Group By

M

mufasa

New Member
Joined
Mar 30, 2008
Messages
5
HI There

I have 3 columns in my my new Car WorkSheet: Model, Dealer, Price. I want to group it by Model, then Dealer and the total price per dealer per model.

How do I accomplish this?

The data is as follows:
It is Basically a simple long spreadsheet A1 = Model, B1 = Dealer, C1 = price

Corolla..............X........................$200
Corolla..............Y........................$300
Corolla..............X........................$100
Ford.................Z........................$500
Ford.................X........................$250
Ford.................Z........................$200
VolksWagen.......y........................$150
VolksWagen.......y........................$350
VolksWagen.......y........................$350

The output should be the same but with summing the price
Corolla..............X........................$300
Corolla..............Y........................$300
Ford.................Z........................$700
Ford.................X........................$250
Volkswagen........y........................$850
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hi & welcome,

I would suggest a pivot table will do exactly what you need.

Pop the Make and Model as Row items and then the Price as a Data item (it should default to Sum).

You might need to take subtotalling off on the Make and/or Model field for it to show how you want. Double click on the field name and select Subtotals...None to do this.

It ends up like this when I did it:

<TABLE style="WIDTH: 156pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=208 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=114 height=17>Sum of Price</TD><TD class=xl23 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 44pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=59></TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 26pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=35></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Make</TD><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Model</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Corolla</TD><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">X</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>300</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl26 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Y</TD><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>300</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Ford</TD><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">X</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>250</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl26 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Z</TD><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>700</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>VolksWagen</TD><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Y</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>850</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Grand Total</TD><TD class=xl29 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2400</TD></TR></TBODY></TABLE>

Hope it helps,

Dom
 
Last edited:
Upvote 0
Thx, Can I export a Pivot Table to a CSV file?

Domski said:
Hi & welcome,

I would suggest a pivot table will do exactly what you need.

Pop the Make and Model as Row items and then the Price as a Data item (it should default to Sum).

You might need to take subtotalling off on the Make and/or Model field for it to show how you want. Double click on the field name and select Subtotals...None to do this.

It ends up like this when I did it:

<table style="width: 156pt; border-collapse: collapse;" x:str="" border="0" cellpadding="0" cellspacing="0" width="208"><colgroup><col style="width: 86pt;" width="114"><col style="width: 44pt;" width="59"><col style="width: 26pt;" width="35"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; width: 86pt; height: 12.75pt; background-color: transparent;" height="17" width="114">Sum of Price</td><td class="xl23" style="border-style: solid none none; border-color: black rgb(235, 233, 237) rgb(235, 233, 237); border-width: 0.5pt medium medium; width: 44pt; background-color: transparent;" width="59">
</td><td class="xl24" style="border-style: solid solid none; border-color: black black rgb(235, 233, 237); border-width: 0.5pt 0.5pt medium; width: 26pt; background-color: transparent;" width="35">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; height: 12.75pt; background-color: transparent;" height="17">Make</td><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; background-color: transparent;">Model</td><td class="xl24" style="border-style: solid solid none; border-color: black black rgb(235, 233, 237); border-width: 0.5pt 0.5pt medium; background-color: transparent;">Total</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; height: 12.75pt; background-color: transparent;" height="17">Corolla</td><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; background-color: transparent;">X</td><td class="xl24" style="border-style: solid solid none; border-color: black black rgb(235, 233, 237); border-width: 0.5pt 0.5pt medium; background-color: transparent;" x:num="" align="right">300</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl25" style="border-style: none none none solid; border-color: rgb(235, 233, 237) rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: medium medium medium 0.5pt; height: 12.75pt; background-color: transparent;" height="17">
</td><td class="xl26" style="border-style: none none none solid; border-color: rgb(235, 233, 237) rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: medium medium medium 0.5pt; background-color: transparent;">Y</td><td class="xl27" style="border-style: none solid; border-color: rgb(235, 233, 237) black; border-width: medium 0.5pt; background-color: transparent;" x:num="" align="right">300</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; height: 12.75pt; background-color: transparent;" height="17">Ford</td><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; background-color: transparent;">X</td><td class="xl24" style="border-style: solid solid none; border-color: black black rgb(235, 233, 237); border-width: 0.5pt 0.5pt medium; background-color: transparent;" x:num="" align="right">250</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl25" style="border-style: none none none solid; border-color: rgb(235, 233, 237) rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: medium medium medium 0.5pt; height: 12.75pt; background-color: transparent;" height="17">
</td><td class="xl26" style="border-style: none none none solid; border-color: rgb(235, 233, 237) rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: medium medium medium 0.5pt; background-color: transparent;">Z</td><td class="xl27" style="border-style: none solid; border-color: rgb(235, 233, 237) black; border-width: medium 0.5pt; background-color: transparent;" x:num="" align="right">700</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; height: 12.75pt; background-color: transparent;" height="17">VolksWagen</td><td class="xl22" style="border-style: solid none none solid; border-color: black rgb(235, 233, 237) rgb(235, 233, 237) black; border-width: 0.5pt medium medium 0.5pt; background-color: transparent;">Y</td><td class="xl24" style="border-style: solid solid none; border-color: black black rgb(235, 233, 237); border-width: 0.5pt 0.5pt medium; background-color: transparent;" x:num="" align="right">850</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl28" style="border-style: solid none solid solid; border-color: black rgb(235, 233, 237) black black; border-width: 0.5pt medium 0.5pt 0.5pt; height: 12.75pt; background-color: transparent;" height="17">Grand Total</td><td class="xl29" style="border-style: solid none; border-color: black rgb(235, 233, 237); border-width: 0.5pt medium; background-color: transparent;">
</td><td class="xl30" style="border: 0.5pt solid black; background-color: transparent;" x:num="" align="right">2400</td></tr></tbody></table>

Hope it helps,

Dom
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Incorporate SUM by cell color in a formula with HSTACK, MAP, TEXTJOIN and LAMBDA.
Replies
3
Views
335
Lixxy
L
J
  • Question
Send emails to group leaders about fund used by their members:
Replies
0
Views
229
JojoGurkha
J
P
  • Question
Update Formula so it calculates correctly
Replies
1
Views
288
StephenCrump
S
P
  • Solved
Keep W-L of card game played over time
Replies
6
Views
448
Preacherman771
P
E
  • Question
FIFO Macro giving different results each time it runs
Replies
7
Views
849
Lee Wei Sin
L

Forum statistics

Threads
1,215,917
Messages
6,127,703
Members
449,399
Latest member
VEVE4014

Share this page

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
Back
Top