Sumproduct And Right Function

[srizki]

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

 

[sailepaty]

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]

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]

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]

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]

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

 

[Domenic]

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]

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]

C7:C1188 has 4 characters only. B10 has characters like, 186000-1416 etc.

thanks

 

[srizki]

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