Malinda Klein
asked on
What is the best way to group and total in Excel?
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.
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.
There is also a SUBTOTAL function in the Outline Group of the Data Tab.
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.
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.
ASKER
This is great! One question is how to make fields Country-City show as 1 group rather than individually as groups?
ASKER
ASKER
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.
ASKER
Should I create a new question for this part?
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
No problem, happy to help!
Choose where you want the pivot table then insert it via the 'Insert' tab.
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.
Now select the Price check box and it will allow you to view the prices in a grouped, nested view.