excelexcel-formulapivot-table

How to GROUP BY Multiple Columns and COUNT in Excel

I want to group 3 columns (Fruit, Color & Vendor) and get their corresponding group count in excel, without using any VBA code, but just simply using Excel functions.

Fruit Vendor Ledger Table:

ID Fruit Color Vendor
1 Apple Red Rocket Apples
2 Apple Red Rocket Apples
3 Apple Yellow Rocket Apples
4 Apple Green Rocket Apples
5 Apple Green Rocket Apples
6 Apple Green Rocket Apples
7 Apple Green Sweet Greens
8 Apple Green Sweet Greens
9 Orange Orange Tangerines
10 Orange Orange Tangerines
11 Orange Orange Tangerines
12 Banana Yellow Phils
13 Banana Yellow Phils
14 Banana Green Brighton
15 Banana Green Brighton
16 Banana Green Brighton

Expected result:

Fruit Color Vendor Count
Apple Red Rocket Apples 2
Apple Yellow Rocket Apples 1
Apple Green Rocket Apples 3
Apple Green Sweet Greens 2
Orange Orange Tangerines 3
Banana Yellow Phils 2
Banana Green Brighton 3

In T-SQL, I would have written the SQL Query as follows. I wanted to know the function or feature by which I could achieve the same in Excel.

SELECT [Fruit], [Color], [Vendor], COUNT(1)
FROM [dbo].[FruitVendorTable] WITH (NOLOCK)
GROUP BY [Fruit], [Color], [Vendor]
Solution

  • Posting the answer, on behalf of @ScottCraner and @MayukhBhattacharya. Thank you both!

    1. Pivot the Table
    2. Design Tab > Subtotals > Do Not Show Subtotals
    3. Design Tab > Reports Layout > Show in Tabular Form
    4. Design Tab > Reports Layout > Repeat all Item Labels

    Pivot Table results