What is the best way to group and total in Excel?

Malinda Klein
Malinda Klein used Ask the Experts™
on
I have a table with columns A,B,C,D,E,F,G.  Column A-F are categories and are sorted first on A, 2nd on B and so on through column F.  G is a price.

I want to count and total column G for rows that match on all columns A-F.

So for a table that contains columns like Country      State      County      City      Street House      Price(actual column names are different and make little sense to someone outside the company),
how do I count the number of houses on a street and subtotal the price?  The problem is that you could have two cities with the same street number.  These two cities could be adjacent in the sorted rows.  So I can't just count and subtotal when the street changes since sometimes the streets will be the  same while the cities are different.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
You can do a Pivot table for this.

Choose where you want the pivot table then insert it via the 'Insert' tab.
pivot1.png
It will ask what data to use, just enter in the name of your table or select the range.

When the Pivot Table appears, check the items you want to use as the grouping criteria.  You can rearrange their order if it's not what you want after you select them, but it should go left to right.
pivot2.png
Now select the Price check box and it will allow you to view the prices in a grouped, nested view.
pivot3.png
Roy CoxGroup Finance Manager

Commented:
There is also a SUBTOTAL function in the Outline Group of the Data Tab.
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
Looking at the new comment, I forgot to mention how to add the count.

After we have the pivot table, if I drag the column I want to count by (say, Country) to the Values section I get the aggregate counts.
pivot5.png
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
This is great!  One question is how to make fields Country-City show as 1 group rather than individually as groups?

Author

Commented:
I've managed to change to Outline form and repeat labels which is close but doesn't fill in the other cells.  Unfortunately this is not quite what accounting wants to receive so I still have to fill in the empty cells.

Author

Commented:
Should I create a new question for this part?
Co-Founder and Chief Architect
Top Expert 2016
Commented:
No worries, I can answer here.  What I would do is:

Create a new column in your table
pivot_2-1.png
Then set the formula to combine the two rows you want.
pivot_2-2.png
Now you should be able to stack it in your pivot.  Note that it will create a new grouping for any difference.
pivot_2-3.png
I don't believe you can show them side by side otherwise.

To edit the empty cells, right click and go to 'Pivot Options'
pivot_2-4.png
Then you can edit the formatting and etc on the tabs here.  For empty cells, you can show something with the option down here.
pivot_2-5.png

Author

Commented:
Excellent instructions and the screen shots made it so easy for me!  You really took the guess work and confusion right out of the problem.  Thank you so much for your help!
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
No problem, happy to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial