Combining SUMPRODUCT with INDEXMATCH

cuddy89

New Member
Joined
Nov 26, 2019
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.

SUMPRODUCT query.png
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
48,260
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=SUMPRODUCT(C5:C9,INDEX(D5:K9,,MATCH(B1,D3:K3)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,260
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Ë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
Joined
Jun 12, 2014
Messages
48,260
Office Version
  1. 365
Platform
  1. Windows
Personally I'd stick to an index/match rather than putting a volatile function into an array formula.
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,260
Office Version
  1. 365
Platform
  1. Windows
If you are having a problem with XL2BB then please start a thread in the "About this board" section.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,282
Messages
5,546,960
Members
410,765
Latest member
faris99
Top