How to GROUP BY Multiple Columns and COUNT in Excel

Question

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]

Answer 1

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

Answer 2

Perhaps with Excel Formulas try:

---

• Formula used in cell F2

=LET(
    α, B2:B17 & "|" & C2:C17 &
        "|" & D2:D17,
    UNIQUE(
        HSTACK(
            B2:B17,
            C2:C17,
            D2:D17,
            MMULT(
                N(
                    TOROW(α) =
                        α
                ),
                SEQUENCE(
                    ROWS(α),
                    ,
                    ,
                    0
                )
            )
        )
    )
)

---

• Using LET() function makes easier to read as well define variables, thus improving performance and reduces the use of redundant calculations.
• α variable is defined for the concatenated columns of B , C and D, this created so as we can use in the following logic of our functions.
• Using MMULT() function to return the matrix product of two arrays, the first one is the comparison of the α with the same α which is now converted into a single row, which returns TRUE and FALSE for the match and non matched ones. using the N() function we are converting it into a 1 and 0 so as the MMULT() can understand, the other array being the one returned using SEQUENCE() & ROWS() function. Ultimately this will give us number of counts for each corresponding groups.
• Finally, using HSTACK() we merge all the three columns and the one returned using above into one array. Since it will have duplicate groups with respective counts, therefore using UNIQUE() to exclude them.

---

When the above answer was posted, the GROUPBY() and PIVOTBY() function was not launched in MS365 Office Insiders version, but if its enabled now, then can use as shown below as well:

=GROUPBY(B2:D17,B2:B17,ROWS,,0)

---