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&"'!D1:D6")))

Hope this helps!

In article <[email protected]>,
"ramana" <[email protected]> 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

[ramana]

Hi ,

Thanks for the replies. I will work on it.

Regards

Ramana