help with formula=)

[Brian from Maui]

How about this?

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP
File Edit View Insert Options Tools Data Window Help About
B8C8D8B9C9D9B10C10D10 =
	A	B	C	D	E	F	G
1	*	*	*	*	*	*	*
2	*	dogs	cats	fish	insects	horses	birds
3	Market*A	7	5	3	5	4	6
4	Market*B	4	4	2	4	7	9
5	Market*C	1	6	2	3	5	3
6	*	*	*	*	*	*	*
7	*	Tier*1	Tier*2	Tier*3	*	*	*
8	Market*A	12	13	5	*	*	*
9	Market*B	8	18	4	*	*	*
10	Market*C	7	10	3	*	*	*
11	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*
13	Tier*1	Tier*2	Tier*3	*	*	*	*
14	dogs	fish	insects	*	*	*	*
15	cats	horses	*	*	*	*	*
16	*	birds	*	*	*	*	*
Sheet1 *

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

If you're Tier's change, change the names, not the formula.

[Aladin Akyurek]

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
B16C16D16B17C17D17B18C18D18 =
	A	B	C	D	E	F	G	H	I	J	K
1	Data*Table							*	*	*	*
2	*	19.99	25.99	30.99	49.99	250	1000	*	*	*	*
3	Market*A	7	5	3	5	4	6	*	*	*	*
4	Market*B	4	4	2	4	7	9	*	*	*	*
5	Market*C	1	6	2	3	5	3	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	Tier*Value	*
15	*	Tier*1	Tier*2	Tier*3	*	*	*	*	Tier*1	Tier*2	Tier*3
16	Market*A	12	13	5	*	*	*	*	19.99	30.99	49.99
17	Market*B	8	18	4	*	*	*	*	25.99	250	*
18	Market*C	7	10	3	*	*	*	*	*	1000	*
19	*	*	*	*	*	*	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The formula in B16, which is copied across then down, is:

=SUMPRODUCT(SUMIF($B$2:$G$2,INDEX($I$16:$K$18,0,MATCH(B$15,$I$15:$K$15,0)),INDEX($B$3:$G$5,MATCH($A16,$A$3:$A$5,0),0)))

[Brian from Maui]

Aladin,

Very nice....that was my second option!

[Aladin Akyurek]

Originally Posted by Brian from Maui

Aladin,

Very nice....that was my second option!

I thought you were out smoking and yet managed to post before me.

[sidel80]

WOW...perfect. Thank you soooooo much! This exactly what I was looking for.