Sumproduct And Right Function

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,831
Office Version
  1. 365
Platform
  1. Windows
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
=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:p></o:p>
<o:p> </o:p>
Explanation, the two sheets are “Capital Expenditure & Approval Report”, and “<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice: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:p></o:p>
The formula above works perfectly if I do not have to use =RIGHT function.<o:p></o:p>
<o:p> </o:p>
How can I ammend the formula to work.<o:p></o:p>
<o:p> </o:p>
Thanks.<o:p></o:p>
Sohail
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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:p></o:p>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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))


 
Upvote 0
Thanks Iliace,
It did not work, I am checking if there is anything wrong with the original formula?
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
C7:C1188 has 4 characters only. B10 has characters like, 186000-1416 etc.

thanks
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top