# Sumproduct works in one column, but not another...

#### Gr8Scott

##### New Member
I have a spreadsheet that is intended to look up a value using three criteria from a long spreadsheet with 6000 rows that could someday extend out to 10000 rows of data. Here is the formula that works.
=SUMPRODUCT(--([RNP_SAFETY_ONHAND.xls]Sheet1!\$A\$2:\$A\$10000=\$C8),--([RNP_SAFETY_ONHAND.xls]Sheet1!\$B\$2:\$B\$10000=\$D8),--([RNP_SAFETY_ONHAND.xls]Sheet1!\$C\$2:\$C\$10000=\$E8),([RNP_SAFETY_ONHAND.xls]Sheet1!\$D\$2:\$D\$10000))

Here is the almost identical formula just a few cells to the right hand side that doesn't work properly. The only difference is that the fourth array is in column E instead of Column D.
=SUMPRODUCT(--([RNP_SAFETY_ONHAND.xls]Sheet1!\$A\$2:\$A\$10000=\$C8),--([RNP_SAFETY_ONHAND.xls]Sheet1!\$B\$2:\$B\$10000=\$D8),--([RNP_SAFETY_ONHAND.xls]Sheet1!\$C\$2:\$C\$10000=\$E8),([RNP_SAFETY_ONHAND.xls]Sheet1!\$E\$2:\$E\$10000))

It returns a zero value even though I know the data would return numbers like 1200 etc easily. I'm completely stumped. I have searched the net over and found no reason why this wouldn't work.

I'll need this formula to work in versions as old as 2003 and I will have many cells like it as this formula carries down as many as 400 rows at a shot in other worksheets. I'm trying to use the SumProduct command because it will work without having to actively update or open excel files. It is supposed to just work. Is the number of formulas or criteria causing the problem?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Have you tried entering it as an array formula, using CTRL + SHIFT + ENTER ?

Hello Gr8Scott, welcome to MrExcel

"array entry" shouldn't be required for that formula. Are you sure the entries in Sheet1!\$E\$2:\$E\$10000 are numeric? If they are text-formatted they may look like numbers but the result of your SUMPRODUCT would be zero.

Try using "Text to columns" to convert that column to numeric

Select column then use

Data > Text to columns > Finish

Hello Gr8Scott, welcome to MrExcel

"array entry" shouldn't be required for that formula. Are you sure the entries in Sheet1!\$E\$2:\$E\$10000 are numeric? If they are text-formatted they may look like numbers but the result of your SUMPRODUCT would be zero.

Try using "Text to columns" to convert that column to numeric

Select column then use

Data > Text to columns > Finish

I think you nailed the problem here. My data looked numeric, but Column E turned out to be entirely text when Column D was entirely numeric. Even though it showed numbers, it was a text only column for some weird reason. I'm going to have to massage the way this data is output to make sure that it exports numbers in column E so my formulas will work.

Thanks for the warm welcome and for the valuable help.

SUCCESS!!! That fixed it. Thank you again for the help!

Last edited:

Replies
3
Views
180
Replies
6
Views
212
Replies
1
Views
450
Replies
18
Views
237
Replies
8
Views
299

1,203,756
Messages
6,057,161
Members
444,908
Latest member
Jayrey

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

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