Excel Grouping and sub toals, multiple columns

I have 2 columns in Excel with about 300 rows, one being headed 'Size', with entries small, medium, large, XL.
The other headed 'Colour', with 6 different colours entered.
I want to produce sub totals breaking down the group Size and Colour i.e 'Small, Blue = 23', 'Small - red = 16', Medium blue = 4' etc.
I can only seem to get sub totalling working for one column of results.
Paul BarrettMDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Subtotals just gives one of the count but with a formula

=OFFSET(A4,-1,1,1,1)&", "&OFFSET(A4,-1,2,1,1)

used in this example could give the result you want (first sort your data by Size and color


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sajid Shaik MSystem AdminCommented:
you can try it with filters or pivot tables
if we get the sample table we can do further more...

all the best
Rodney EndrigaData AnalystCommented:

For what you are asking, I believe the PIVOT TABLE method would be helpful.

Pivot Tables can summarize a Table of data or a Range of Specified cells. You will be able to group your data by SIZE/COLOUR or vice versa as well as add more columns, as needed. It will give you the subtotals by Category and/or Column and you can also click the totals within the pivot table to see the RAW DATA as the chosen group.

If more data is added at a later time/date, you will be able to REFRESH the Pivot Table in order to capture the most current data. This will keep your Pivot Table with the most recent data that is entered.
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Rgonzo1971's comment #a41191825

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Paul BarrettMDAuthor Commented:
Apologies for the dealy in getting back to this, I agree with the administrative comment.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.