sumproduct problem

[hanasamo]

=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

 

[XLGibbs]

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.

 

[PaddyD]

"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]

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)