How to group multiple items in to three groups.

[ramana]

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

 

[ramana]

Hi Everbody,

can I use OR function to group the items, if there are more than 30
items what shall I do.

Regards

Ramana

 

[anar_baku]

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.

 

[Domenic]

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&"'!D16")))

Hope this helps!

 

[ramana]

Hi ,

Thanks for the replies. I will work on it.

Regards

Ramana