Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to group multiple items in to three groups.
Hi Everybody,
I have 12 sheets for the twelve months in a year. In each sheet I have items like apple, banana, bread, choclate, onion, carret ...etc All of the items I can group it in to three categories i.e. fruits(apple,banana), baked food(bread,choclate), Vegetables(onion,carret). Now I want to get the sum of the items in categories. An Ex I'm Giving Here. A B C apple 2 56 onion 1 15 choclate 5 45 bread 3 24 carret 2 12 banana 1 6 .. .. .. etc vegetable (need a formula to sum onion&carret...etc) friuts (need a formula to sum apple&banana...etc) bakedfood (need a formula to sum choclate&bread...etc) f any body have any suggetions olase tell me. Thanks and regards Ramana |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to group multiple items in to three groups.
Hi Everbody,
can I use OR function to group the items, if there are more than 30 items what shall I do. Regards Ramana |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to group multiple items in to three groups.
One way is to add another column to your table and in each cell next to the item indicate whether it's a F (fruit), V (vegetable) or B (bakedfood) and then use SUMIF function to sum up the applicable items. -- anar_baku ------------------------------------------------------------------------ anar_baku's Profile: http://www.excelforum.com/member.php...o&userid=18259 View this thread: http://www.excelforum.com/showthread...hreadid=489333 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to group multiple items in to three groups.
Assuming that you want to sum Column C where the corresponding value in
Column A belongs to the Fruit category, let E1:E2 contain Apple and Banana, G1:R1 contain a list of your sheet names, and try... =SUMPRODUCT(--(ISNUMBER(MATCH(T(OFFSET(INDIRECT("'"&G1:R1&"'!A1: A6"),ROW( INDIRECT("A1:A6"))-1,0,1)),E1:E2,0))),N(OFFSET(INDIRECT("'"&G1:R1&"'! C1:C 6"),ROW(INDIRECT("C1:C6"))-1,0,1))) For your Vegetable and Bakedfood categories, create another two lists containing items belonging to their respective categories, and change E1:E2 to refer to your new range for your particular category. As you can see, the formula is rather complex, and very expensive. A better alternative would be to set up your worksheets as follows... apple fruit 2 56 onion vegetable 1 15 choclate bakedfood 5 45 bread bakedfood 3 24 carret vegetable 2 12 banana fruit 1 6 Then, let G1:G12 contain your list of sheet names, I1:I3 contain Vegetable, Fruit, and Bakedfood, and enter the following formula in K1, and copy down: =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$1:$G$12&"'!B1:B6 "),I1,INDIRECT("'"&$G$1 :$G$12&"'!D1:D6"))) Hope this helps! In article . com, "ramana" wrote: Hi Everybody, I have 12 sheets for the twelve months in a year. In each sheet I have items like apple, banana, bread, choclate, onion, carret ..etc All of the items I can group it in to three categories i.e. fruits(apple,banana), baked food(bread,choclate), Vegetables(onion,carret). Now I want to get the sum of the items in categories. An Ex I'm Giving Here. A B C apple 2 56 onion 1 15 choclate 5 45 bread 3 24 carret 2 12 banana 1 6 . . . etc vegetable (need a formula to sum onion&carret...etc) friuts (need a formula to sum apple&banana...etc) bakedfood (need a formula to sum choclate&bread...etc) f any body have any suggetions olase tell me. Thanks and regards Ramana |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to group multiple items in to three groups.
Hi ,
Thanks for the replies. I will work on it. Regards Ramana |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drag multiple data items into pivot | Excel Worksheet Functions | |||
how to group several items in different groups to be placed in a c | Excel Discussion (Misc queries) | |||
multiple items in database | Excel Worksheet Functions | |||
Can't group pivot table items by month in Excel | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |