# sumproduct problem

#### hanasamo

##### Board Regular
=SUMPRODUCT(--('Original Data'!\$D\$2:\$D\$1779=Sheet1!B11),--(LEFT('Original Data'!\$A\$2:\$A\$1779,4)=Sheet1!A11),'Original Data'!\$B\$2:\$B\$1779)

The above is the formula in cell D11, and I need to grag it down. The format in A11 is set as general, D11 can't display the right result until I convert the format of column A to text. The problem is that the right result can only be displayed after I double click A11, A12, A13, .............

Is there anyway to solve this problem? Thanks in advance

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### XLGibbs

##### Well-known Member
SUMPRODUCT(--('Original Data'!\$D\$2:\$D\$1779=Sheet1!B11),--(LEFT('Original Data'!\$A\$2:\$A\$1779,4)=Trim(Sheet1!A11)),'Original Data'!\$B\$2:\$B\$1779)

Maybe?

I have encountered this before in excel as well, and have yet to find an explanation that warrantes "editing" each cell by hitting F2 then enter in each cell.

If the data in A11 is imported from a text file, modify the text import and preview the data where you can specify the imported format...i think you can change it from General to Text format in the import editor.

##### MrExcel MVP
"The problem is that the right result can only be displayed after I double click A11, A12, A13,"

What's in A11 etc?

#### barry houdini

##### MrExcel MVP
I assume A11 is numeric...

try

=SUMPRODUCT(--('Original Data'!\$D\$2:\$D\$1779=Sheet1!B11),--(LEFT('Original Data'!\$A\$2:\$A\$1779,4)+0=Sheet1!A11),'Original Data'!\$B\$2:\$B\$1779)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,994
Messages
5,834,775
Members
430,321
Latest member
yemisimi11

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