How to pickup value from non continuous columns?

[xlmaniac]

Dear All,
I do have a sample data set across A7:M12 as follows:-

	Week1	Week1	Week1	Week2	Week2	Week2	Week3	Week3	Week3	Week4	Week4	Week4
Store(A8)	A	B	C	A	B	C	A	B	C	A	B	C
1	61%	64%	39%	64%	63%	41%	66%	66%	44%	60%	62%	37%
2	82%	59%	48%	82%	63%	52%	81%	54%	44%	70%	67%	46%
3	82%	63%	51%	77%	82%	63%	61%	61%	38%	63%	70%	44%
Overall	67%	60%	40%	68%	63%	43%	67%	60%	40%	62%	63%	39%(M12)

I want to pickup value from the above table as per following summary across B1:F5.
The value from Column Headers under C needs to be picked up under every week.[C=A*B]
Can somebody help me out with the formulae across columns C to F which can populate the desired results from the data set?

Store(B1)	Week1(C1)	Week2(D1)	Week3(E1)	Week4(F1)
1	39%	41%	44%	37%
2	48%	52%	44%	46%
3	51%	63%	38%	44%
Overall	40%	43%	40%	39%

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Store(B1)	Week1	Week2	Week3	Week4
2		1	39%	41%	44%	37%
3		2	48%	52%	44%	46%
4		3	51%	63%	38%	44%
5		Overall	40%	43%	40%	39%
6
7		Week1	Week1	Week1	Week2	Week2	Week2	Week3	Week3	Week3	Week4	Week4	Week4
8	Store(A8)	A	B	C	A	B	C	A	B	C	A	B	C
9	1	61%	64%	39%	64%	63%	41%	66%	66%	44%	60%	62%	37%
10	2	82%	59%	48%	82%	63%	52%	81%	54%	44%	70%	67%	46%
11	3	82%	63%	51%	77%	82%	63%	61%	61%	38%	63%	70%	44%
12	Overall	67%	60%	40%	68%	63%	43%	67%	60%	40%	62%	63%	39%
Sheet2

Cell Formulas
Range	Formula
C2:F5	C2	=SUMPRODUCT($B$9:$M$12*(--($B$7:$M$7=C$1)*($B$8:$M$8="C")*($A$9:$A$12=$B2)))

 

[xlmaniac]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Store(B1)	Week1	Week2	Week3	Week4
2		1	39%	41%	44%	37%
3		2	48%	52%	44%	46%
4		3	51%	63%	38%	44%
5		Overall	40%	43%	40%	39%
6
7		Week1	Week1	Week1	Week2	Week2	Week2	Week3	Week3	Week3	Week4	Week4	Week4
8	Store(A8)	A	B	C	A	B	C	A	B	C	A	B	C
9	1	61%	64%	39%	64%	63%	41%	66%	66%	44%	60%	62%	37%
10	2	82%	59%	48%	82%	63%	52%	81%	54%	44%	70%	67%	46%
11	3	82%	63%	51%	77%	82%	63%	61%	61%	38%	63%	70%	44%
12	Overall	67%	60%	40%	68%	63%	43%	67%	60%	40%	62%	63%	39%
Sheet2

Cell Formulas
Range	Formula
C2:F5	C2	=SUMPRODUCT($B$9:$M$12*(--($B$7:$M$7=C$1)*($B$8:$M$8="C")*($A$9:$A$12=$B2)))

Thank you AlanY for the excellent solution.
Really appreciate your support!!

 

[AlanY]

you're welcome

Thank you AlanY for the excellent solution.
Really appreciate your support!!