COUNTIF function query for multiple colums

M

Malcolm Gordon

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.
 
M

Malcolm Gordon

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?
 
M

Malcolm Gordon

Hi Alan,

I changed the ordering and your formula works! Thanks you very much :)
 
A

Alan

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.
 
A

Ashish Mathur

Hi,

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

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

Regards,
 
Top