sumproduct problem

hanasamo

Board Regular
Joined
May 31, 2005
Messages
83
=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
 

Some videos you may like

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
Joined
Feb 25, 2005
Messages
2,446
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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
Joined
Mar 23, 2005
Messages
20,825
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,131
Members
412,305
Latest member
Mozz
Top