Sumproduct and format issues

[Curtis]

I am currently using the formula

=SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$AS$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

 

[Roger Govier]

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.

 

[Curtis]

Then I am Stumped.

Using
=SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000>0)*($AS$5:$AS$4000))

When cell $t$4007 is text or not returns gives me a value 0.00 when text and
#value! when formated as general

I am not sure if it is worth mentioning but thee number typed in cell
$t$4007 refers to that value in column J

ce

 

[Roger Govier]

Hi Curtis

Send me a copy of the file and I will see if I can see what the problem
is.
To send direct, remove NOSPAM from my address

 

[Roger Govier]

Hi Curtis

File received and returned.
For the benefit of others, the problem was caused by there being and 10
rows in column J with alpha entries.
Once these were changed to numeric, the formula worked fine.

Another Sumproduct formula on the sheet was returning #VALUE errors.
In this case, based upon values in column E. Column E contained formulae
=IF(AV5=4.75,1," ")
the fact that it was returning space " " rather than null "" was the
problem.
As this was a hidden column, I changed it to
=IF(AV5=4.75,1,0)
rather than using null.