Summarizing Data in Excel

[jjohn0085]

Hi All,

I'm managing a restaurant and from my POS system i can pull out daily sales report, using the same data i want to estimate my Raw Materials Expenses. And to do so I need help with appropriate formula which can give me a summary of "Sub Category" under "column C" and "Category" under "Column B" on a separate worksheet.
Example: I want to know total count of items sold under Sub Category consisting of "Chicken", "Beef" etc to estimate my Raw Material Expenses
I want to know total count of items sold under Category falling under "Desi Chinese", "North Indian" etc

 

[alansidman]

Cannot manipulate data in a picture. Please use XL2BB to upload data. See the link in my signature.

 

[CA_Punit]

You can do it using Pivot Table.
Please post the data using xl2bb as we have to type the whole thing

 

[jjohn0085]

Product	Category	Sub Category	Qty	Price	Total Price
Karak Tea	Cafeteria	Rainbow Milk	164	QAR 1.00​	QAR 164.00​
Karak Tea Large	Cafeteria	Rainbow Milk	31	QAR 2.00​	QAR 62.00​
Coffee	Cafeteria	Rainbow Milk	6	QAR 2.00​	QAR 12.00​
Coffee Medium	Cafeteria	Rainbow Milk	6	QAR 3.00​	QAR 18.00​
Water Small	Cooler	Cooler	18	QAR 1.00​	QAR 18.00​
Water Big	Cooler	Cooler	9	QAR 2.00​	QAR 18.00​
Pepsi	Cooler	Cooler	1	QAR 3.00​	QAR 3.00​
7up	Cooler	Cooler	2	QAR 3.00​	QAR 6.00​
Mountain Dew	Cooler	Cooler	2	QAR 3.00​	QAR 6.00​
Chicken Steam Momo - L	Desi Chinese	Chicken	1	QAR 11.00​	QAR 11.00​
Chicken Fry Momo -S	Desi Chinese	Chicken	2	QAR 7.00​	QAR 14.00​
Malabar Parotta	South Indian	Maida	268	QAR 1.00​	QAR 268.00​
Wheat Parotta	South Indian	Chakki Atta	18	QAR 2.00​	QAR 36.00​
Aalu Paratha	North Indian	Chakki Atta	4	QAR 5.00​	QAR 20.00​
Chappathi	South Indian	Chappathi	44	QAR 1.00​	QAR 44.00​
Dosa Set	Breakfast	Dosa Batter	5	QAR 5.00​	QAR 25.00​
Thattu Dosa	Breakfast	Dosa Batter	6	QAR 5.00​	QAR 30.00​
Masala Dosa	Breakfast	Dosa Batter	10	QAR 6.00​	QAR 60.00​
Ghee Roast	Breakfast	Dosa Batter	2	QAR 6.00​	QAR 12.00​
Appam/ Dosa	Breakfast	Dosa Batter	6	QAR 1.00​	QAR 6.00​
Idly Set	Breakfast	Dosa Batter	8	QAR 6.00​	QAR 48.00​
Poori Masala	Breakfast	Chakki Atta	6	QAR 6.00​	QAR 36.00​
Kadala/ Cheruppayar	Breakfast	Brown Chana	11	QAR 4.00​	QAR 44.00​
Green Peas Masala	South Indian	Green Peas Frozen	1	QAR 10.00​	QAR 10.00​
Egg Roast	Breakfast	Egg	5	QAR 5.00​	QAR 25.00​
Aloo Bhaji	Breakfast	Vegetable	4	QAR 4.00​	QAR 16.00​
Dal Fry	North Indian	Vegetable	2	QAR 7.00​	QAR 14.00​
Beef Curry	South Indian	Beef	1	QAR 12.00​	QAR 12.00​
Egg Single Omelette	Breakfast	Egg	3	QAR 2.00​	QAR 6.00​
Egg Double Omelette	Breakfast	Egg	1	QAR 3.00​	QAR 3.00​
Lemon	Cafeteria	Fruits	2	QAR 3.00​	QAR 6.00​
Lemon Mint	Cafeteria	Fruits	1	QAR 4.00​	QAR 4.00​
Orange Juice	Cafeteria	Fruits	1	QAR 4.00​	QAR 4.00​
Avacado	Cafeteria	Fruits	6	QAR 7.00​	QAR 42.00​
Mango	Cafeteria	Fruits	1	QAR 10.00​	QAR 10.00​
Orange Fresh	Cafeteria	Fruits	4	QAR 8.00​	QAR 32.00​
Mix ****tail	Cafeteria	Fruits	2	QAR 8.00​	QAR 16.00​
Chocolate	Cafeteria	Chocolate	1	QAR 10.00​	QAR 10.00​
Fruit Salad	Cafeteria	Fruits	1	QAR 10.00​	QAR 10.00​
Oasis Fruit Salad	Cafeteria	Fruits	1	QAR 12.00​	QAR 12.00​
Samona Cheese Sandwich	Cafeteria	Cheese	1	QAR 2.00​	QAR 2.00​
Parotta Egg Sandwich	Cafeteria	Egg	23	QAR 4.00​	QAR 92.00​
Parotta Chicken Sandwich	Cafeteria	Chicken	7	QAR 5.00​	QAR 35.00​
Oasis Special	Cafeteria	Chicken	1	QAR 8.00​	QAR 8.00​
Chicken Burger	Cafeteria	Chicken Burger	9	QAR 5.00​	QAR 45.00​
Chicken Fillet	Cafeteria	Chicken	1	QAR 7.00​	QAR 7.00​
Veg Burger	Cafeteria	Vegetable Burger	2	QAR 6.00​	QAR 12.00​
Club Normal	Cafeteria	Chicken	2	QAR 10.00​	QAR 20.00​
Meals Parcel - Barik	Lunch	Lunch	10	QAR 9.00​	QAR 90.00​

 

[jjohn0085]

@alansidman & @CA_Punit
I have posted the data, hope it can be of help

 

[CA_Punit]

Is this you want

Book1
	A	B	C	D	E	F	G	H	I	J	K
2	Product	Category	Sub Category	Qty	Price	Total Price		Category	Sub Category	Sum of Qty
3	Karak Tea	Cafeteria	Rainbow Milk	164	QAR 1.00	QAR 164.00		Breakfast	Brown Chana	11
4	Karak Tea Large	Cafeteria	Rainbow Milk	31	QAR 2.00	QAR 62.00			Chakki Atta	6
5	Coffee	Cafeteria	Rainbow Milk	6	QAR 2.00	QAR 12.00			Dosa Batter	37
6	Coffee Medium	Cafeteria	Rainbow Milk	6	QAR 3.00	QAR 18.00			Egg	9
7	Water Small	Cooler	Cooler	18	QAR 1.00	QAR 18.00			Vegetable	4
8	Water Big	Cooler	Cooler	9	QAR 2.00	QAR 18.00		Cafeteria	Cheese	1
9	Pepsi	Cooler	Cooler	1	QAR 3.00	QAR 3.00			Chicken	11
10	7up	Cooler	Cooler	2	QAR 3.00	QAR 6.00			Chicken Burger	9
11	Mountain Dew	Cooler	Cooler	2	QAR 3.00	QAR 6.00			Chocolate	1
12	Chicken Steam Momo - L	Desi Chinese	Chicken	1	QAR 11.00	QAR 11.00			Egg	23
13	Chicken Fry Momo -S	Desi Chinese	Chicken	2	QAR 7.00	QAR 14.00			Fruits	19
14	Malabar Parotta	South Indian	Maida	268	QAR 1.00	QAR 268.00			Rainbow Milk	207
15	Wheat Parotta	South Indian	Chakki Atta	18	QAR 2.00	QAR 36.00			Vegetable Burger	2
16	Aalu Paratha	North Indian	Chakki Atta	4	QAR 5.00	QAR 20.00		Cooler	Cooler	32
17	Chappathi	South Indian	Chappathi	44	QAR 1.00	QAR 44.00		Desi Chinese	Chicken	3
18	Dosa Set	Breakfast	Dosa Batter	5	QAR 5.00	QAR 25.00		Lunch	Lunch	10
19	Thattu Dosa	Breakfast	Dosa Batter	6	QAR 5.00	QAR 30.00		North Indian	Chakki Atta	4
20	Masala Dosa	Breakfast	Dosa Batter	10	QAR 6.00	QAR 60.00			Vegetable	2
21	Ghee Roast	Breakfast	Dosa Batter	2	QAR 6.00	QAR 12.00		South Indian	Beef	1
22	Appam/ Dosa	Breakfast	Dosa Batter	6	QAR 1.00	QAR 6.00			Chakki Atta	18
23	Idly Set	Breakfast	Dosa Batter	8	QAR 6.00	QAR 48.00			Chappathi	44
24	Poori Masala	Breakfast	Chakki Atta	6	QAR 6.00	QAR 36.00			Green Peas Frozen	1
25	Kadala/ Cheruppayar	Breakfast	Brown Chana	11	QAR 4.00	QAR 44.00			Maida	268
26	Green Peas Masala	South Indian	Green Peas Frozen	1	QAR 10.00	QAR 10.00		Grand Total		723
27	Egg Roast	Breakfast	Egg	5	QAR 5.00	QAR 25.00
28	Aloo Bhaji	Breakfast	Vegetable	4	QAR 4.00	QAR 16.00
29	Dal Fry	North Indian	Vegetable	2	QAR 7.00	QAR 14.00
30	Beef Curry	South Indian	Beef	1	QAR 12.00	QAR 12.00
31	Egg Single Omelette	Breakfast	Egg	3	QAR 2.00	QAR 6.00
32	Egg Double Omelette	Breakfast	Egg	1	QAR 3.00	QAR 3.00
33	Lemon	Cafeteria	Fruits	2	QAR 3.00	QAR 6.00
34	Lemon Mint	Cafeteria	Fruits	1	QAR 4.00	QAR 4.00
35	Orange Juice	Cafeteria	Fruits	1	QAR 4.00	QAR 4.00
36	Avacado	Cafeteria	Fruits	6	QAR 7.00	QAR 42.00
37	Mango	Cafeteria	Fruits	1	QAR 10.00	QAR 10.00
38	Orange Fresh	Cafeteria	Fruits	4	QAR 8.00	QAR 32.00
39	Mix ****tail	Cafeteria	Fruits	2	QAR 8.00	QAR 16.00
40	Chocolate	Cafeteria	Chocolate	1	QAR 10.00	QAR 10.00
41	Fruit Salad	Cafeteria	Fruits	1	QAR 10.00	QAR 10.00
42	Oasis Fruit Salad	Cafeteria	Fruits	1	QAR 12.00	QAR 12.00
43	Samona Cheese Sandwich	Cafeteria	Cheese	1	QAR 2.00	QAR 2.00
44	Parotta Egg Sandwich	Cafeteria	Egg	23	QAR 4.00	QAR 92.00
45	Parotta Chicken Sandwich	Cafeteria	Chicken	7	QAR 5.00	QAR 35.00
46	Oasis Special	Cafeteria	Chicken	1	QAR 8.00	QAR 8.00
47	Chicken Burger	Cafeteria	Chicken Burger	9	QAR 5.00	QAR 45.00
48	Chicken Fillet	Cafeteria	Chicken	1	QAR 7.00	QAR 7.00
49	Veg Burger	Cafeteria	Vegetable Burger	2	QAR 6.00	QAR 12.00
50	Club Normal	Cafeteria	Chicken	2	QAR 10.00	QAR 20.00
51	Meals Parcel - Barik	Lunch	Lunch	10	QAR 9.00	QAR 90.00
Sheet7

 

[alansidman]

Using Power Query/Get and Transform, I grouped the data by Category and Sub-Category. Mcode as follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Category", type text}, {"Sub Category", type text}, {"Qty", Int64.Type}, {"Price", type text}, {"Total Price", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category", "Sub Category"}, {{"Quantity", each List.Sum([Qty]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Category", Order.Ascending}, {"Sub Category", Order.Ascending}})
in
    #"Sorted Rows"

Book4
	A	B	C
1	Category	Sub Category	Quantity
2	Breakfast	Brown Chana	11
3	Breakfast	Chakki Atta	6
4	Breakfast	Dosa Batter	37
5	Breakfast	Egg	9
6	Breakfast	Vegetable	4
7	Cafeteria	Cheese	1
8	Cafeteria	Chicken	11
9	Cafeteria	Chicken Burger	9
10	Cafeteria	Chocolate	1
11	Cafeteria	Egg	23
12	Cafeteria	Fruits	19
13	Cafeteria	Rainbow Milk	207
14	Cafeteria	Vegetable Burger	2
15	Cooler	Cooler	32
16	Desi Chinese	Chicken	3
17	Lunch	Lunch	10
18	North Indian	Chakki Atta	4
19	North Indian	Vegetable	2
20	South Indian	Beef	1
21	South Indian	Chakki Atta	18
22	South Indian	Chappathi	44
23	South Indian	Green Peas Frozen	1
24	South Indian	Maida	268
Sheet2