# Combining SUMPRODUCT with INDEXMATCH

#### cuddy89

##### New Member
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.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=SUMPRODUCT(C5:C9,INDEX(D5:K9,,MATCH(B1,D3:K3)))``

You're welcome.

#### hnsd24_CN

##### Board Regular

Ëxpample:you can use OFFSET
test.xlsm
ABCDEFGHI
1B
2ABCD
312345
423456
534567
645678
756789
8
985
Sheet3
Cell Formulas
RangeFormula
A9A9=SUMPRODUCT(A3:A7,OFFSET(C3,,MATCH(A1,C2:I2,)-1,5))

#### Fluff

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

#### hnsd24_CN

##### Board Regular
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,

Replies
1
Views
111
Replies
5
Views
87
Replies
1
Views
119
Replies
5
Views
82
Replies
10
Views
146