Sumproduct returning #Value! when linked to another workbook?

[duboost]

Hi,

I'm using a sumproduct formula to sum the numbers in a column based on criteria in other columns. For example...

A..........B..........C
Up.......On.........10
Up.......On.........15
Down...On.........20

=SUMPRODUCT(--(A2:A4="UP"),--(B2:B4="On"),--(C2:C4)

should return 25. This works fine if the data and sumproduct function are in the same workbook, but if they are in separate workbooks, sumproduct returns #Value!. Is sumproduct not able to do this when linked to other workbooks? I know if i cut out the last part of the function (C2:C4), it will give me a correct count (2) even when linked to another workbook, but when trying to get a sum of column C, it returns an error. Any ideas? Thanks

 

[duboost]

ok it seems to work only until u reach a certain number of rows. If I needed to do this for 4,000 rows, is there another way to calculate this if sumproduct won't allow me to calculate that many rows?

 

[duboost]

ok nevermind lol. Apparently there was one cell in column C within the 4,000 that has a #value! rather than a number and that was causing the error with sumproduct...