# Sumproduct And Right Function

#### srizki

##### Well-known Member
I trying to match two columns of one table to two column of another in another workbook. One of the columns that needs to be matched has number with other numbers and I need only to match the four numbers at far end, therefore I used this formula.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$B\$7:\$B\$1188=D10)*('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$C\$7:\$C\$1188=RIGHT(B10,4)*'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$N\$7:\$N\$1188))<o></o>
<o> </o>
Explanation, the two sheets are “Capital Expenditure & Approval Report”, and “<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:PersonName w:st="on">Ken</st1:PersonName> Report”, my formula is in <st1:PersonName w:st="on">Ken</st1:PersonName> report and the report has number like 18600-1413 and I need to match only 1413. <o></o>
The formula above works perfectly if I do not have to use =RIGHT function.<o></o>
<o> </o>
How can I ammend the formula to work.<o></o>
<o> </o>
Thanks.<o></o>
Sohail

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### sailepaty

##### Active Member
Try this,

=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$B\$7:\$B\$1188=D10)*('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$C\$7:\$C\$1188=RIGHT(B10,4)+0)*'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$N\$7:\$N\$1188))

It changes the result of the Right function to value instead of text.

Regards<o></o>

#### srizki

##### Well-known Member
Thanks Sailepaty, although it did not work, I am sure there must be something else in the formula that is causing the problem, I will keep trying with changes. If you comeup with something, please let me know

Thanks again.

#### sailepaty

##### Active Member
I would recommend don’t use the double and * in the same SUMPRODUCT function. So, you can try any one of these.

=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$B\$7:\$B\$1188=D10),--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$C\$7:\$C\$1188=RIGHT(B10,4)+0),'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$N\$7:\$N\$1188))

Or

=SUMPRODUCT(('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$B\$7:\$B\$1188=D10)*('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$C\$7:\$C\$1188=RIGHT(B10,4)+0)*'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$N\$7:\$N\$1188))

Hope it works

#### iliace

##### Well-known Member
Parentheses probably.

=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$B\$7:\$B\$1188=D10)*('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$C\$7:\$C\$1188=RIGHT(B10,4))*('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$N\$7:\$N\$1188))

#### srizki

##### Well-known Member
Thanks Iliace,
It did not work, I am checking if there is anything wrong with the original formula?

#### Domenic

##### MrExcel MVP
Does the last 4 characters in B10 need to match the last 4 characters in C7:C1188? If so, try...

=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$B\$7:\$B\$1188=D10),--(RIGHT('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$C\$7:\$C\$1188,4)=RIGHT(B10,4)),'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$N\$7:\$N\$1188)

Hope this helps!

#### srizki

##### Well-known Member
Thanks Domenic,

I have not tried you formula yet, the file, 'Capital Expenditure Report' has only the last four numbers, the other file where the formuls is, needs to use right function, the number in the column are 186000-1416.
I will ammend your formula accordingly.

Thanks

#### srizki

##### Well-known Member
C7:C1188 has 4 characters only. B10 has characters like, 186000-1416 etc.

thanks

#### srizki

##### Well-known Member
Hi Domenic,
I used your formula as is, and it worked perfectly, although It only requires right function on one sheet, where the lookup value is.

I tried to use your formula removing, (see in red)

=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$B\$7:\$B\$1188=D10),--(RIGHT('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$C\$7:\$C\$1188,4)=RIGHT(B10,4)),'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!\$N\$7:\$N\$1188)

but it did not work, so I used as is, Although it worked, but I know it can be shortened by removing one of the right function, what should the formula be, after taking one Right function off.'

Thanks

Sohail

Replies
9
Views
527
Replies
3
Views
833
Replies
6
Views
491
Replies
3
Views
454
Replies
3
Views
419

1,190,764
Messages
5,982,797
Members
439,798
Latest member
tangojuliet

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