# Sumproduct Instead Of Sumif - Sumif Has Index-Match?

#### emus007

##### New Member
Hi,

I'm wanting to use the sumproduct function for this formula instead of the sumif formula because it doesn't work when referencing an external workbook.
=SUMIF('[01-Payroll Cost Summary.xlsx]Labour-Total'!\$B\$5:\$B\$56,\$P\$2,INDEX('[01-Payroll Cost Summary.xlsx]Labour-Total'!\$C\$4:\$X\$56,0,MATCH(\$K5,'[01-Payroll Cost Summary.xlsx]Labour-Total'!\$C\$4:\$X\$4,0)))

How would I write the index-match part of the formula? I believe the first array would simply be: ('[01-Payroll Cost Summary.xlsx]Labour-Total'!\$B\$5:\$B\$56=\$P\$2).

Thanks,
Elu

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Is it?

=SUMPRODUCT(--('[01-Payroll Cost Summary.xlsx]Labour-Total'!\$B\$5:\$B\$56=\$P\$2),INDEX('[01-Payroll Cost Summary.xlsx]Labour-Total'!\$C\$5:\$X\$56,0,MATCH(\$K5,'[01-Payroll Cost Summary.xlsx]Labour-Total'!\$C\$4:\$X\$4,0)))

Thanks Andrew - that wasn't hard at all.

Very much appreciated.

Replies
1
Views
164
Replies
5
Views
72
Replies
19
Views
239
Replies
4
Views
65
Replies
1
Views
316

1,196,516
Messages
6,015,679
Members
441,915
Latest member
sm Hussaini

### 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