SUMPRODUCT query

[Andy83UK]

Hi, I have been using the following formula in a spreadsheet for a year now and today for some reason it is contantly throwing up the #value error.

I have combed through the data it looks at several times today and so far can't find anything that would cause this error. The formula I'm using is

=SUMPRODUCT(--('[newssheet.xlsm]Source Data'!$C:$C="L"),--('[newssheet.xlsm]Source Data'!$Z:$Z<>""),--('[newssheet.xlsm]Source Data'!$R:$R="Enterprise"))

Column C in the sheet its pulling from contains formulas to give either blanks, "L" or "N/R" results, column C is text based and column Z is dates

Thanks for your help.

 

[Jon von der Heyden]

Hello

Couple of possibilities:

You may have an error in one of the evaluation ranges (i.e. C:C,Z:Z,R:R in your referenced workbook and worksheet). You can check by highlighting these columns > F5 > Special > Formulas (you could try constants too) > Error. You need to eliminate any such errors.

You are referencing whole columns. This is acceptable in versions 2007+. You cannot use whole columns in prior versions.

 

[Andy83UK]

I haven't been ab;e to find any inconsistencies, but I will try another look, it is the newest version of 2007. As I said this has been working all year until now.

 

[Jon von der Heyden]

There isn't any fault with your formula. Based on what you say I think the only plausible explanation is that there is a #VALUE! error in one of the ranges that your formula references.