Combining SUMPRODUCT with INDEXMATCH

[cuddy89]

I would be grateful for some assistance with this formula that I am struggling to write properly.

Please see the excel table below. My aim is to rewrite the second part of the SUMPRODUCT (K5:K9) so that it looks up the relevant column based on the date in B1.
I have already achieved this with the formula in cell P2, but cant get it to work as part of the SUMPRODUCT.

So I'm looking for something like:

=SUMPRODUCT(C5:C9,column based on date in B1 matched with range D3:K3)/SUM(INDEX(D5:K9,,MATCH(B1,D3:K3)))

Thanks in advance for any suggestions.

 

[Fluff]

How about

=SUMPRODUCT(C5:C9,INDEX(D5:K9,,MATCH(B1,D3:K3)))

 

[cuddy89]

=SUMPRODUCT(C5:C9,INDEX(D5:K9,,MATCH(B1,D3:K3)))

Thanks Fluff!

 

[Fluff]

You're welcome.

 

[hnsd24_CN]

Ëxpample:you can use OFFSET

test.xlsm
	A	B	C	D	E	F	G	H	I
1	B
2			A		B		C		D
3	1		2		3		4		5
4	2		3		4		5		6
5	3		4		5		6		7
6	4		5		6		7		8
7	5		6		7		8		9
8
9	85
Sheet3

Cell Formulas
Range	Formula
A9	A9	=SUMPRODUCT(A3:A7,OFFSET(C3,,MATCH(A1,C2:I2,)-1,5))

 

[Fluff]

Personally I'd stick to an index/match rather than putting a volatile function into an array formula.

 

[hnsd24_CN]

Personally I'd stick to an index/match rather than putting a volatile function into an array formula.

I have another problem. Sometimes when I use xl2bb or insert excel function in post, it doesn't display correctly. I'm sure I've used these steps correctly,

 

[Fluff]

If you are having a problem with XL2BB then please start a thread in the "About this board" section.