Hi Malcolm,
Glad it works for you, but it shouldn't make any difference whether the
value in a cell is a number you've entered or the result of a formula, it's
either a negative value or it isn't.
I'm not sure what you mean by 'changing the ordering' though!
Regards,
Alan.
"Malcolm Gordon" wrote in message
...
Hi Alan,
I changed the ordering and your formula works! Thanks you very much :-)
"Malcolm Gordon" wrote:
Hi Alan,
Thanks for the response. Unfortunately, it didn't work. I should add that
the value s in Column J are based on formulas, does this present a
problem?
"Alan" wrote:
Try
=SUMPRODUCT(--(A1:A20=1),--(J1:J20<0))
Regards,
Alan.
"Malcolm Gordon" <Malcolm wrote in
message
...
How do I count the number of occurrences of "1" in the A column each
time
there is a negative value in the J column on the same row.
i.e. if cell A1 has a value of 1 and cell J1 has a value of -5 I want
this
cell to be counted.