Formula Generator | Excel

Excel Formula for Sorting Food Sales Volume

Learn how to use Excel's SUMIFS and SORT functions to calculate and sort total sales volumes for specific food types, including Breakfast, Bunny Chow, and Braai, based on your dataset.

Prompt

write a function that can help sort total sales volume for breakfast , bunny chow braai and poetkjie from this data Type_of_Food	Product	Sales_Volume
Breakfast	Farm Breakfast	200
Breakfast	French Toast	150
Breakfast	Eggs Benedict	120
Breakfast	Classic Breakfast	180
Breakfast	Omelette	160
Braai	Boerewors	300
Braai	Steak	250
Braai	Grilled Chicken	220
Braai	Lamb Chops	180
Braai	Snoek	150
Bunny Chow	Chicken	180
Bunny Chow	Steak	160
Bunny Chow	Lamb	140
Bunny Chow	Vegetarian	120
Bunny Chow	Mince	130
Potjiekos	Beef	220
Potjiekos	Chicken	200
Potjiekos	Lamb	180
Potjiekos	Vegetarian	150
Potjiekos	Venison	170
Deserts	Malva Pudding	250
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	220
Breakfast	Farm Breakfast	190
Breakfast	French Toast	140
Breakfast	Eggs Benedict	110
Breakfast	Classic Breakfast	170
Breakfast	Omelette	150
Braai	Boerewors	280
Braai	Steak	230
Braai	Grilled Chicken	200
Braai	Lamb Chops	160
Braai	Snoek	140
Bunny Chow	Chicken	160
Bunny Chow	Steak	150
Bunny Chow	Lamb	130
Bunny Chow	Vegetarian	110
Bunny Chow	Mince	120
Potjiekos	Beef	200
Potjiekos	Chicken	180
Potjiekos	Lamb	160
Potjiekos	Vegetarian	130
Potjiekos	Venison	150
Deserts	Malva Pudding	240
Deserts	Crème Brulee	190
Deserts	Ice Cream	170
Deserts	Waffle	140
Deserts	Peppermint Crisp Tart	210
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	270
Braai	Steak	220
Braai	Grilled Chicken	190
Braai	Lamb Chops	150
Braai	Snoek	130
Bunny Chow	Chicken	170
Bunny Chow	Steak	160
Bunny Chow	Lamb	140
Bunny Chow	Vegetarian	120
Bunny Chow	Mince	130
Potjiekos	Beef	200
Potjiekos	Chicken	180
Potjiekos	Lamb	160
Potjiekos	Vegetarian	130
Potjiekos	Venison	150
Deserts	Malva Pudding	240
Deserts	Crème Brulee	190
Deserts	Ice Cream	170
Deserts	Waffle	140
Deserts	Peppermint Crisp Tart	210
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	300
Braai	Steak	250
Braai	Grilled Chicken	220
Braai	Lamb Chops	180
Braai	Snoek	150
Bunny Chow	Chicken	170
Bunny Chow	Steak	160
Bunny Chow	Lamb	140
Bunny Chow	Vegetarian	120
Bunny Chow	Mince	130
Potjiekos	Beef	200
Potjiekos	Chicken	180
Potjiekos	Lamb	160
Potjiekos	Vegetarian	130
Potjiekos	Venison	150
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	300
Braai	Steak	250
Braai	Grilled Chicken	220
Braai	Lamb Chops	180
Braai	Snoek	150
Bunny Chow	Chicken	180
Bunny Chow	Steak	170
Bunny Chow	Lamb	150
Bunny Chow	Vegetarian	130
Bunny Chow	Mince	140
Potjiekos	Beef	220
Potjiekos	Chicken	200
Potjiekos	Lamb	180
Potjiekos	Vegetarian	150
Potjiekos	Venison	170
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	300
Braai	Steak	250
Braai	Grilled Chicken	220
Braai	Lamb Chops	180
Braai	Snoek	150
Bunny Chow	Chicken	180
Bunny Chow	Steak	170
Bunny Chow	Lamb	150
Bunny Chow	Vegetarian	130
Bunny Chow	Mince	140
Potjiekos	Beef	220
Potjiekos	Chicken	200
Potjiekos	Lamb	180
Potjiekos	Vegetarian	150
Potjiekos	Venison	170
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	300
Braai	Steak	250
Braai	Grilled Chicken	220
Braai	Lamb Chops	180
Braai	Snoek	150
Bunny Chow	Chicken	180
Bunny Chow	Steak	170
Bunny Chow	Lamb	150
Bunny Chow	Vegetarian	130
Bunny Chow	Mince	140
Potjiekos	Beef	220
Potjiekos	Chicken	200
Potjiekos	Lamb	180
Potjiekos	Vegetarian	150
Potjiekos	Venison	170
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	300
Braai	Steak	250
Braai	Grilled Chicken	220
Braai	Lamb Chops	180
Braai	Snoek	150
Bunny Chow	Chicken	180
Bunny Chow	Steak	170
Bunny Chow	Lamb	150
Bunny Chow	Vegetarian	130
Bunny Chow	Mince	140
Potjiekos	Beef	220
Potjiekos	Chicken	200
Potjiekos	Lamb	180
Potjiekos	Vegetarian	150
Potjiekos	Venison	170
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	280
Braai	Steak	230
Braai	Grilled Chicken	200
Braai	Lamb Chops	160
Braai	Snoek	140
Bunny Chow	Chicken	180
Bunny Chow	Steak	170
Bunny Chow	Lamb	150
Bunny Chow	Vegetarian	130
Bunny Chow	Mince	140
Potjiekos	Beef	210
Potjiekos	Chicken	190
Potjiekos	Lamb	170
Potjiekos	Vegetarian	140
Potjiekos	Venison	160
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	290
Braai	Steak	240
Braai	Grilled Chicken	210
Braai	Lamb Chops	170
Braai	Snoek	150
Bunny Chow	Chicken	180
Bunny Chow	Steak	170
Bunny Chow	Lamb	150
Bunny Chow	Vegetarian	130
Bunny Chow	Mince	140
Potjiekos	Beef	210
Potjiekos	Chicken	190
Potjiekos	Lamb	170
Potjiekos	Vegetarian	140
Potjiekos	Venison	160
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230
Breakfast	Farm Breakfast	136
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	65
Braai	Boerewors	290
Braai	Steak	240
Braai	Grilled Chicken	210
Braai	Lamb Chops	170
Braai	Snoek	150
Bunny Chow	Chicken	180
Bunny Chow	Steak	170
Bunny Chow	Lamb	150
Bunny Chow	Vegetarian	130
Bunny Chow	Mince	140
Potjiekos	Beef	210
Potjiekos	Chicken	190
Potjiekos	Lamb	170
Potjiekos	Vegetarian	140
Potjiekos	Venison	160
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230
Breakfast	Farm Breakfast	138
Breakfast	French Toast	67
Breakfast	Eggs Benedict	99
Breakfast	Classic Breakfast	89
Breakfast	Omelette	66
Braai	Boerewors	320
Braai	Steak	270
Braai	Grilled Chicken	240
Braai	Lamb Chops	200
Braai	Snoek	170
Bunny Chow	Chicken	180
Bunny Chow	Steak	170
Bunny Chow	Lamb	150
Bunny Chow	Vegetarian	130
Bunny Chow	Mince	140
Potjiekos	Beef	210
Potjiekos	Chicken	190
Potjiekos	Lamb	170
Potjiekos	Vegetarian	140
Potjiekos	Venison	160
Deserts	Malva Pudding	260
Deserts	Crème Brulee	200
Deserts	Ice Cream	180
Deserts	Waffle	150
Deserts	Peppermint Crisp Tart	230

Answer

Excel Formula to Sort Total Sales Volume for Specified Food Types

Objective

To create a formula in Excel that calculates and sorts the total sales volume for the food types: Breakfast, Bunny Chow, and Braai from the provided dataset.

Dataset Structure

The dataset consists of the following columns:

Type_of_Food
Product
Sales_Volume

Sample Data (for reference)

Type_of_Food	Product	Sales_Volume
Breakfast	Farm Breakfast	200
Bunny Chow	Chicken	180
Braai	Boerewors	300
...	...	...

Formula Creation

You can use the SUMIFS function to calculate total sales volume based on the food types and then sort the results. Here’s how:

Step 1: Calculate Total Sales Volume

To calculate the total sales volume for Breakfast, Bunny Chow, and Braai:

=SUMIFS(C:C, A:A, "Breakfast") + SUMIFS(C:C, A:A, "Bunny Chow") + SUMIFS(C:C, A:A, "Braai")

Explanation

SUMIFS function: This function sums up values in a specified range based on one or more criteria.
C:C: This represents the range containing Sales_Volume values.
A:A: This represents the range containing Type_of_Food.
Criteria: The specific food types "Breakfast", "Bunny Chow", and "Braai".

Step 2: Sort the Results

To sort the calculated totals for the specific food types, follow these steps:

Create a summary table where first column lists the food types and the second column applies the total formula.
Use the SORT function:

=SORT(A1:B3, 2, FALSE)

Assuming your summary table is in the range from A1 to B3, where Column A contains food types and Column B contains their respective total sales volume.

Explanation

SORT function: This function sorts a range or array.
A1:B3: This is the range to be sorted.
2: This indicates that the second column (sales volume) will be the sorting key.
FALSE: This signifies descending order.

Practical Illustration

Create a summary table in cells E1 to F3 as follows:

Food Type Total Sales Volume

Breakfast =SUMIFS(C:C, A:A, "Breakfast")

Bunny Chow =SUMIFS(C:C, A:A, "Bunny Chow")

Braai =SUMIFS(C:C, A:A, "Braai")
Sort the results in cell G1 using: =SORT(E1:F3, 2, FALSE)

Food Type	Total Sales Volume
Breakfast	=SUMIFS(C:C, A:A, "Breakfast")
Bunny Chow	=SUMIFS(C:C, A:A, "Bunny Chow")
Braai	=SUMIFS(C:C, A:A, "Braai")

This will result in a sorted list of total sales volumes for Breakfast, Bunny Chow, and Braai in descending order.

Conclusion

By utilizing the SUMIFS function along with sorting capabilities in Excel, you can efficiently aggregate and sort sales data for specific food types, allowing for insightful analysis and decision-making based on sales performance. For further Excel skills enhancement, consider resources available on the Enterprise DNA Platform.

Description

Learn how to use Excel's SUMIFS and SORT functions to calculate and sort total sales volumes for specific food types, including Breakfast, Bunny Chow, and Braai, based on your dataset.