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

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

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

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

How to group multiple items in to three groups.
 

Hi ,

Thanks for the replies. I will work on it.

Regards

Ramana