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

[Gr8Scott]

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?

 

[j844929]

Have you tried entering it as an array formula, using CTRL + SHIFT + ENTER ?

 

[barry houdini]

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

 

[Gr8Scott]

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.

edited to add:
SUCCESS!!! That fixed it. Thank you again for the help!