# 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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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

Replies
3
Views
95
Replies
21
Views
188
Replies
3
Views
177
Replies
3
Views
145
Replies
13
Views
255