COUNTIF function query for multiple colums
 

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.

COUNTIF function query for multiple colums
 

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.

COUNTIF function query for multiple colums
 

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.

COUNTIF function query for multiple colums
 

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.

COUNTIF function query for multiple colums
 

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.

COUNTIF function query for multiple colums
 

Hi,

Try the following array formula (Ctrl+Shift+Enter)

SUM(IF((A3:A7=1)*(J3:J7<0),1,0))

Regards,

"Malcolm Gordon" wrote:

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.