Hi Curtis

With a small set of sample data entered in A1:B5
with Numeric
1 5
1 -6
1 -7
Text
1 8
1 -3

=SUMPRODUCT((--A1:A5=C1)*(B1:B5<0)*(B1:B5))
correctly returns -16 with a numeric 1 in cell C1, as all values in
column A have been coerced to numeric values.

=SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5))
returns -13 with a numeric 1 in cell C1 because the text 1's have not
been coerced to Numeric values.

=SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5))
returns -3 with a Text 1 entered in C1

Each of these is correct, in terms of what formula and data are used.

It is only the first one that returns the correct value in the context
of what you are wanting.
If you are getting #Value errors, there must be further problems with
your data.
I would not trust the result you are obtaining without the double unary
minuses and the text entry in T4007, albeit it is not returning you an
error.

--
Regards

Roger Govier


"Curtis" wrote in message
...
I am currently using the formula

=SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000))

Column J is employee Id and could be formated as text or number ( raw
file)
Column AS is formated as a number
Cell T4007 is formated as a number

The problem I have I get an #VALUE! unless I remove the -- in the
formula
above and enter the value in cell t4007 as text

Any advice?

ce