2, "↑", "↓")), PERCENTOF): Performs COUNT calculation on the Sales column (count analysis) Applies LAMBDA formula on the Ratings column to display "↑" if count is greater than 2 or "↓" otherwise Performs PERCENTOF calculation on the Profit column (percentage analysis) Sets parameter total_depth to 0 (no totals)"/>
Refer to the following examples to see how to use the GROUPBY function to perform data aggregation using a single formula:
- J3 cell formula: `=GROUPBY(A1:B62, E1:E62, SUM, 3, 2)`
- Uses A1:B62 range (Year and Category) as parameter **row_fields**
- Selects E1:E62 range (Sales) as parameter **values** for aggregation
- Applies SUM aggregation as parameter **function** to the Sales column
- Sets parameter **field_headers** to 3 (show field headers)
- Sets parameter **total_depth** to 2 (show both grand totals and subtotals)
- P3 cell formula: `=GROUPBY(B1:B62, E1:G62, VSTACK(COUNT, LAMBDA(a, IF(COUNT(a)>2, "↑", "↓")), PERCENTOF), , 0)`
- Uses B1:B62 range (Category) as parameter **row_fields**
- Selects E1:G62 range (Sales, Rating, Profit) as parameter **values** for aggregation
- Applies aggregation parameter **function** to the selected values using VSTACK(COUNT, LAMBDA(a, IF(COUNT(a)>2, "↑", "↓")), PERCENTOF):
- Performs COUNT calculation on the Sales column (count analysis)
- Applies LAMBDA formula on the Ratings column to display "↑" if count is greater than 2 or "↓" otherwise
- Performs PERCENTOF calculation on the Profit column (percentage analysis)
- Sets parameter **total_depth** to 0 (no totals)
// Create a new workbook
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getActiveSheet();
Object[][] data = new Object[][]{
{"Year", "Category", "Product", "Status", "Sales", "Rating", "Profit"},
{2023, "Electronics", "Smart TV", "Active", 15000, 4.5, 250},
{2023, "Fashion", "Designer Jeans", "Discontinued", 8000, 4.7, 150},
{2024, "Food", "Organic Granola", "Active", 5000, 4.8, 100},
{2023, "Books", "Bestseller Novel", "Active", 12000, 4.6, 180},
{2024, "Electronics", "Laptop", "Active", 20000, 4.9, 300},
{2023, "Beauty", "Skincare Set", "Active", 7000, 4.4, 120},
{2023, "Home & Garden", "Garden Tools", "Active", 6500, 4.3, 130},
{2024, "Health", "Fitness Tracker", "Active", 9500, 4.6, 190},
{2023, "Toys", "Action Figure", "Active", 4800, 4.7, 95},
{2024, "Automotive", "Car Accessories", "Active", 3200, 4.5, 65},
{2023, "Sports", "Basketball", "Active", 7600, 4.8, 150},
{2024, "Office Supplies", "Notebooks", "Active", 11000, 4.4, 220},
{2023, "Pet Supplies", "Dog Food", "Active", 5600, 4.6, 110},
{2024, "Music", "Headphones", "Active", 13000, 4.9, 260},
{2023, "Outdoor", "Camping Tent", "Active", 4400, 4.5, 88},
{2024, "Jewelry", "Silver Necklace", "Active", 2800, 4.7, 56},
{2023, "Tools", "Power Drill", "Active", 3900, 4.4, 78},
{2024, "Baby", "Stroller", "Active", 1700, 4.6, 34},
{2023, "Kitchen", "Blender", "Active", 2500, 4.8, 50},
{2024, "Clothing", "Casual Shirt", "Active", 6200, 4.5, 124},
{2023, "Art", "Oil Paintings", "Active", 1900, 4.7, 38},
{2024, "Hobbies", "Model Trains", "Active", 3100, 4.4, 62},
{2023, "Tech Gadgets", "Smart Watch", "Active", 7300, 4.6, 146},
{2024, "Travel", "Luggage", "Active", 4600, 4.8, 92},
{2023, "Home Decor", "Wall Clock", "Active", 2200, 4.5, 44},
{2024, "Fitness", "Yoga Mat", "Active", 5700, 4.7, 114},
{2023, "Gardening", "Plant Pots", "Active", 3400, 4.4, 68},
{2024, "Audio", "Speakers", "Active", 8900, 4.6, 178},
{2023, "Lighting", "LED Bulbs", "Active", 4300, 4.8, 86},
{2024, "Gaming", "Video Games", "Active", 16000, 4.5, 320},
{2023, "Furniture", "Sofa", "Active", 5500, 4.7, 110},
{2024, "Health", "Vitamins", "Active", 7100, 4.4, 142},
{2023, "Office", "Desk Chair", "Active", 6300, 4.6, 126},
{2024, "Books", "Educational Textbooks", "Active", 9200, 4.8, 184},
{2023, "Tools", "Screwdriver Set", "Active", 3700, 4.5, 74},
{2024, "Home & Garden", "Patio Furniture", "Active", 4900, 4.7, 98},
{2023, "Automotive", "Car Seat Covers", "Active", 2600, 4.4, 52},
{2024, "Electronics", "Smart Home System", "Active", 10500, 4.6, 210},
{2023, "Health", "Blood Pressure Monitor", "Active", 3800, 4.8, 76},
{2024, "Beauty", "Makeup Kit", "Active", 5900, 4.5, 118},
{2023, "Music", "Turntable", "Active", 2100, 4.7, 42},
{2024, "Toys", "Board Games", "Active", 3300, 4.4, 66},
{2023, "Clothing", "Winter Coat", "Active", 4700, 4.6, 94},
{2024, "Food", "Gourmet Chocolates", "Active", 7400, 4.8, 148},
{2023, "Home Decor", "Curtains", "Active", 2900, 4.5, 58},
{2024, "Pet Supplies", "Cat Litter", "Active", 6100, 4.7, 122},
{2023, "Outdoor", "Hiking Boots", "Active", 3500, 4.4, 70},
{2024, "Kitchen", "Slow Cooker", "Active", 8200, 4.6, 164},
{2023, "Toys", "Dolls", "Active", 4400, 4.8, 88},
{2024, "Books", "Poetry Collection", "Active", 5300, 4.5, 106},
{2023, "Health", "Massage Chair", "Active", 6600, 4.7, 132},
{2024, "Home & Garden", "Fire Pit", "Active", 7700, 4.4, 154},
{2023, "Automotive", "Car Wash Kit", "Active", 4000, 4.6, 80},
{2024, "Office Supplies", "Highlighters", "Active", 5200, 4.8, 104},
{2023, "Tools", "Hammer", "Active", 3200, 4.5, 64},
{2024, "Beauty", "Hair Dryer", "Active", 6800, 4.7, 136},
{2023, "Kitchen", "Coffee Maker", "Active", 4600, 4.4, 92},
{2024, "Toys", "Remote Control Cars", "Active", 7900, 4.6, 158},
{2023, "Home Decor", "Picture Frames", "Active", 3300, 4.8, 66},
{2024, "Electronics", "Router", "Active", 8500, 4.5, 170},
{2023, "Clothing", "Suit", "Active", 5700, 4.7, 114}
};
sheet.getRange("A1:G" + data.length).setValue(data);
sheet.getRange("A1:G1").getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(200, 214, 239));
sheet.getRange("J1").setValue("The basic usage with conditional formatting");
sheet.getRange("J2").setFormula("=FORMULATEXT(J3)");
sheet.getRange("J3").setFormula2("=GROUPBY(A1:B62,E1:E62,SUM,3,2)");
IFormatCondition conditionalFormatting1 = (IFormatCondition) sheet.getRange("J3:L48").getFormatConditions()
.add(FormatConditionType.Expression, FormatConditionOperator.None, "$J3=\"Year\"", null);
conditionalFormatting1.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(183, 221, 121));
conditionalFormatting1.getFont().setBold(true);
IFormatCondition conditionalFormatting2 = (IFormatCondition) sheet.getRange("J3:L48").getFormatConditions()
.add(FormatConditionType.Expression, FormatConditionOperator.None, "$K3=\"\"", null);
conditionalFormatting2.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(146, 208, 80));
conditionalFormatting2.getFont().setBold(true);
sheet.getRange("P1").setValue("The non-adjacent columns and multiple aggregations with VSTACK");
sheet.getRange("P2").setFormula("=FORMULATEXT(P3)");
sheet.getRange("P3").setFormula2("=GROUPBY(B1:B62,E1:G62,VSTACK(COUNT,LAMBDA(a,IF(COUNT(a)>2,\"↑\",\"↓\")),PERCENTOF),,0)");
IFormatCondition conditionalFormatting3 = (IFormatCondition) sheet.getRange("P3:R95").getFormatConditions()
.add(FormatConditionType.Expression, FormatConditionOperator.None, "$Q3=\"COUNT\"", null);
conditionalFormatting3.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(180, 199, 231));
IFormatCondition conditionalFormatting4 = (IFormatCondition) sheet.getRange("P3:R95").getFormatConditions()
.add(FormatConditionType.Expression, FormatConditionOperator.None, "$Q3=\"CUSTOM\"", null);
conditionalFormatting4.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(216, 228, 188));
IFormatCondition conditionalFormatting5 = (IFormatCondition) sheet.getRange("P3:R95").getFormatConditions()
.add(FormatConditionType.Expression, FormatConditionOperator.None, "$Q3=\"PERCENTOF\"", null);
conditionalFormatting5.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(198, 224, 180));
sheet.getRange("A:AD").setColumnWidth(12.5);
// Save to an excel file
workbook.save("GroupbyFunction.xlsx");
// Create a new workbook
var workbook = Workbook()
val sheet = workbook.getActiveSheet()
val data = arrayOf?>(
arrayOf("Year", "Category", "Product", "Status", "Sales", "Rating", "Profit"),
arrayOf(2023, "Electronics", "Smart TV", "Active", 15000, 4.5, 250),
arrayOf(2023, "Fashion", "Designer Jeans", "Discontinued", 8000, 4.7, 150),
arrayOf(2024, "Food", "Organic Granola", "Active", 5000, 4.8, 100),
arrayOf(2023, "Books", "Bestseller Novel", "Active", 12000, 4.6, 180),
arrayOf(2024, "Electronics", "Laptop", "Active", 20000, 4.9, 300),
arrayOf(2023, "Beauty", "Skincare Set", "Active", 7000, 4.4, 120),
arrayOf(2023, "Home & Garden", "Garden Tools", "Active", 6500, 4.3, 130),
arrayOf(2024, "Health", "Fitness Tracker", "Active", 9500, 4.6, 190),
arrayOf(2023, "Toys", "Action Figure", "Active", 4800, 4.7, 95),
arrayOf(2024, "Automotive", "Car Accessories", "Active", 3200, 4.5, 65),
arrayOf(2023, "Sports", "Basketball", "Active", 7600, 4.8, 150),
arrayOf(2024, "Office Supplies", "Notebooks", "Active", 11000, 4.4, 220),
arrayOf(2023, "Pet Supplies", "Dog Food", "Active", 5600, 4.6, 110),
arrayOf(2024, "Music", "Headphones", "Active", 13000, 4.9, 260),
arrayOf(2023, "Outdoor", "Camping Tent", "Active", 4400, 4.5, 88),
arrayOf(2024, "Jewelry", "Silver Necklace", "Active", 2800, 4.7, 56),
arrayOf(2023, "Tools", "Power Drill", "Active", 3900, 4.4, 78),
arrayOf(2024, "Baby", "Stroller", "Active", 1700, 4.6, 34),
arrayOf(2023, "Kitchen", "Blender", "Active", 2500, 4.8, 50),
arrayOf(2024, "Clothing", "Casual Shirt", "Active", 6200, 4.5, 124),
arrayOf(2023, "Art", "Oil Paintings", "Active", 1900, 4.7, 38),
arrayOf(2024, "Hobbies", "Model Trains", "Active", 3100, 4.4, 62),
arrayOf(2023, "Tech Gadgets", "Smart Watch", "Active", 7300, 4.6, 146),
arrayOf(2024, "Travel", "Luggage", "Active", 4600, 4.8, 92),
arrayOf(2023, "Home Decor", "Wall Clock", "Active", 2200, 4.5, 44),
arrayOf(2024, "Fitness", "Yoga Mat", "Active", 5700, 4.7, 114),
arrayOf(2023, "Gardening", "Plant Pots", "Active", 3400, 4.4, 68),
arrayOf(2024, "Audio", "Speakers", "Active", 8900, 4.6, 178),
arrayOf(2023, "Lighting", "LED Bulbs", "Active", 4300, 4.8, 86),
arrayOf(2024, "Gaming", "Video Games", "Active", 16000, 4.5, 320),
arrayOf(2023, "Furniture", "Sofa", "Active", 5500, 4.7, 110),
arrayOf(2024, "Health", "Vitamins", "Active", 7100, 4.4, 142),
arrayOf(2023, "Office", "Desk Chair", "Active", 6300, 4.6, 126),
arrayOf(2024, "Books", "Educational Textbooks", "Active", 9200, 4.8, 184),
arrayOf(2023, "Tools", "Screwdriver Set", "Active", 3700, 4.5, 74),
arrayOf(2024, "Home & Garden", "Patio Furniture", "Active", 4900, 4.7, 98),
arrayOf(2023, "Automotive", "Car Seat Covers", "Active", 2600, 4.4, 52),
arrayOf(2024, "Electronics", "Smart Home System", "Active", 10500, 4.6, 210),
arrayOf(2023, "Health", "Blood Pressure Monitor", "Active", 3800, 4.8, 76),
arrayOf(2024, "Beauty", "Makeup Kit", "Active", 5900, 4.5, 118),
arrayOf(2023, "Music", "Turntable", "Active", 2100, 4.7, 42),
arrayOf(2024, "Toys", "Board Games", "Active", 3300, 4.4, 66),
arrayOf(2023, "Clothing", "Winter Coat", "Active", 4700, 4.6, 94),
arrayOf(2024, "Food", "Gourmet Chocolates", "Active", 7400, 4.8, 148),
arrayOf(2023, "Home Decor", "Curtains", "Active", 2900, 4.5, 58),
arrayOf(2024, "Pet Supplies", "Cat Litter", "Active", 6100, 4.7, 122),
arrayOf(2023, "Outdoor", "Hiking Boots", "Active", 3500, 4.4, 70),
arrayOf(2024, "Kitchen", "Slow Cooker", "Active", 8200, 4.6, 164),
arrayOf(2023, "Toys", "Dolls", "Active", 4400, 4.8, 88),
arrayOf(2024, "Books", "Poetry Collection", "Active", 5300, 4.5, 106),
arrayOf(2023, "Health", "Massage Chair", "Active", 6600, 4.7, 132),
arrayOf(2024, "Home & Garden", "Fire Pit", "Active", 7700, 4.4, 154),
arrayOf(2023, "Automotive", "Car Wash Kit", "Active", 4000, 4.6, 80),
arrayOf(2024, "Office Supplies", "Highlighters", "Active", 5200, 4.8, 104),
arrayOf(2023, "Tools", "Hammer", "Active", 3200, 4.5, 64),
arrayOf(2024, "Beauty", "Hair Dryer", "Active", 6800, 4.7, 136),
arrayOf(2023, "Kitchen", "Coffee Maker", "Active", 4600, 4.4, 92),
arrayOf(2024, "Toys", "Remote Control Cars", "Active", 7900, 4.6, 158),
arrayOf(2023, "Home Decor", "Picture Frames", "Active", 3300, 4.8, 66),
arrayOf(2024, "Electronics", "Router", "Active", 8500, 4.5, 170),
arrayOf(2023, "Clothing", "Suit", "Active", 5700, 4.7, 114)
)
sheet.getRange("A1:G" + data.size).setValue(data)
sheet.getRange("A1:G1").getInterior().setColor(Color.FromArgb(200, 214, 239))
sheet.getRange("J1").setValue("The basic usage with conditional formatting")
sheet.getRange("J2").setFormula("=FORMULATEXT(J3)")
sheet.getRange("J3").setFormula2("=GROUPBY(A1:B62,E1:E62,SUM,3,2)")
val conditionalFormatting1 = sheet.getRange("J3:L48").getFormatConditions()
.add(
FormatConditionType.Expression,
FormatConditionOperator.None,
"\$J3=\"Year\"",
null
) as IFormatCondition
conditionalFormatting1.getInterior().setColor(Color.FromArgb(183, 221, 121))
conditionalFormatting1.getFont().setBold(true)
val conditionalFormatting2 = sheet.getRange("J3:L48").getFormatConditions()
.add(FormatConditionType.Expression, FormatConditionOperator.None, "\$K3=\"\"", null) as IFormatCondition
conditionalFormatting2.getInterior().setColor(Color.FromArgb(146, 208, 80))
conditionalFormatting2.getFont().setBold(true)
sheet.getRange("P1").setValue("The non-adjacent columns and multiple aggregations with VSTACK")
sheet.getRange("P2").setFormula("=FORMULATEXT(P3)")
sheet.getRange("P3")
.setFormula2("=GROUPBY(B1:B62,E1:G62,VSTACK(COUNT,LAMBDA(a,IF(COUNT(a)>2,\"↑\",\"↓\")),PERCENTOF),,0)")
val conditionalFormatting3 = sheet.getRange("P3:R95").getFormatConditions()
.add(
FormatConditionType.Expression,
FormatConditionOperator.None,
"\$Q3=\"COUNT\"",
null
) as IFormatCondition
conditionalFormatting3.getInterior().setColor(Color.FromArgb(180, 199, 231))
val conditionalFormatting4 = sheet.getRange("P3:R95").getFormatConditions()
.add(
FormatConditionType.Expression,
FormatConditionOperator.None,
"\$Q3=\"CUSTOM\"",
null
) as IFormatCondition
conditionalFormatting4.getInterior().setColor(Color.FromArgb(216, 228, 188))
val conditionalFormatting5 = sheet.getRange("P3:R95").getFormatConditions()
.add(
FormatConditionType.Expression,
FormatConditionOperator.None,
"\$Q3=\"PERCENTOF\"",
null
) as IFormatCondition
conditionalFormatting5.getInterior().setColor(Color.FromArgb(198, 224, 180))
sheet.getRange("A:AD").setColumnWidth(12.5)
// Save to an excel file
workbook.save("GroupbyFunction.xlsx")