# SUMPRODUCT INDEX MATCH not working with closed workbook

#### Gem1979

##### New Member
Hi, would really appreciate some help with the formula below. As I understand it SUMPRODUCT, INDEX and MATCH should all work with a closed workbook but I still get #REF when I close the 2020 Performance Targets file. There are no tables present. Am I missing something? I'm using this to return a year to date figure, would happily consider an alternative!

=SUMPRODUCT(INDEX('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1:\$D\$22,MATCH(\$D12,'[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$C\$1:\$C\$22,0)):INDEX('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1:\$P\$22,MATCH(\$D12,'[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$C\$1:\$C\$22,0),\$I\$8))

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### jasonb75

##### Well-known Member
As far as I'm aware, your formula should work with the other workbook closed. There are some functions that don't work with closed workbooks, but none that you're using. That said, if any precedent ranges contain functions which do not work with closed workbooks then that could possibly be the cause.

When you close the workbook, are the references in the formula changing to show the full path instead of just the workbook name?

What happens if you try a simple formula like below, does that still error when you close the file?

='[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1

#### RoryA

##### MrExcel MVP, Moderator
I don't think that the syntax using INDEX(...) on either side of : to construct a range works with closed workbooks. Perhaps because of volatility.

#### Gem1979

##### New Member
As far as I'm aware, your formula should work with the other workbook closed. There are some functions that don't work with closed workbooks, but none that you're using. That said, if any precedent ranges contain functions which do not work with closed workbooks then that could possibly be the cause.

When you close the workbook, are the references in the formula changing to show the full path instead of just the workbook name?

What happens if you try a simple formula like below, does that still error when you close the file?

='[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1

Thank you Jasonb75, no the simple formula doesn't error when the file is closed. Might be as RoryA says, the range is causing an issue?

#### Gem1979

##### New Member

I don't think that the syntax using INDEX(...) on either side of : to construct a range works with closed workbooks. Perhaps because of volatility.
Thank you RoryA, that could well be the issue.

#### Gem1979

##### New Member
I don't think that the syntax using INDEX(...) on either side of : to construct a range works with closed workbooks. Perhaps because of volatility.
The formula works if I take out the range but obviously don't get the result I need;
=IFERROR(IFERROR(SUMPRODUCT(INDEX('MB ex DSP'!\$D\$1:\$D\$262,MATCH(\$D13,'MB ex DSP'!\$C\$1:\$C\$262,0)),\$I\$8),SUMPRODUCT(INDEX('MB ex DSP'!\$D\$1:\$D\$262,MATCH(\$D13,'MB ex DSP'!\$B\$1:\$B\$262,0)),\$I\$8)),"")

Any thoughts on an alternative? I might give in and just have YTD sums on the closed workbook so I can have a simple index match but I don't want to be beaten

#### jasonb75

##### Well-known Member
Well spotted, Rory! I had looked at the functions used but missed the union.

See if this works, think I have the parentheses in the right places.

=SUMPRODUCT(INDEX('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1:\$P\$22,MATCH(\$D12,'[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$C\$1:\$C\$22,0),0)*(((COLUMN('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1:\$P\$1)-COLUMN('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1)+1)<=\$I\$8))

#### Gem1979

##### New Member
Well spotted, Rory! I had looked at the functions used but missed the union.

See if this works, think I have the parentheses in the right places.

=SUMPRODUCT(INDEX('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1:\$P\$22,MATCH(\$D12,'[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$C\$1:\$C\$22,0),0)*(((COLUMN('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1:\$P\$1)-COLUMN('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!\$D\$1)+1)<=\$I\$8))
Oo you star. it worked! Now I will try and understand it I've not used COLUMN before so thank you for showing me something new. Thank you for your time, much appreciated.

Replies
1
Views
154
Replies
1
Views
138
Replies
11
Views
460
Replies
0
Views
51
Replies
5
Views
239

1,127,865
Messages
5,627,344
Members
416,242
Latest member
Kas O

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back