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
 

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
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)
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top