array formula or sum-product?

[kruiser]

Appears to be something very basic, but I am stumped.

Sorry, can't use html uploader here at work, so will try to keep it as clear and concise as i can.

This is the data I have -

Fruit ; Store ; July_sales
Apples ; 111 ; 20
Oranges ; 222 ; 30
bananas ; 333 ; 40
peaches ; 112 ; 50
Grapes ; 223 ; 60
Pineapple ; 223 ; 70

On a separate sheet, have defined the chains as (I have named these ranges as chain1, chain2 etc)-

chain1 ; chain2 ; chain3
111 ; 222 ; 333
112 ; 223 ;

I simply want to find out the total monthly sales for each chain. Should have the flexibility to add or remove stores to a chain. Need sales for each month separately.

Tried difefrent combinations of sumproduct and array formulas but getting stumped at how to match the condition for second (or third, fourth) store in the chain.

Thanks for any help/ nudge in the right direction.

 

[GlennUK]

A PivotTable makes this very easy. Is it possible for you to alter the data to generate the chain in the data list? Like this:

Excel Workbook
	A	B	C	D	E	F	G
1	Fruit	Store	July_sales	Chain	*	Store	Chain
2	Apples	111	20	chain1	*	111	chain1
3	Oranges	222	30	chain2	*	112	chain1
4	bananas	333	40	chain3	*	222	chain2
5	peaches	112	50	chain1	*	223	chain2
6	Grapes	223	60	chain2	*	333	chain3
7	Pineapple	223	70	chain2	*	*	*
8	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*
11	Sum of July_sales	*	*	*	*	*	*
12	Chain	Total	*	*	*	*	*
13	chain1	70	*	*	*	*	*
14	chain2	160	*	*	*	*	*
15	chain3	40	*	*	*	*	*
16	Grand Total	270	*	*	*	*	*
Sheet21

That's a PivotTable starting at A11 by the way.

 

[kruiser]

Thank you Glenn. I added a working column for the chain along with the data and then it became very easy.

 

[GlennUK]

That's great!