COUNTIF - Two Variables

[ajay_gajree]

Hi

=SUM(($A$6:$A$98="EQUITY OPS")*($B$6:$C$98=1))

I am using the above formula to return the number of times "Equity Ops" and "1" are in the same row.

This is return zero even when I have the corresponding data in the rows,

I am using Ctrl, Shift, Enter to put {} arounf the formula, still no luck,

Can anyone help?

Best regards

Ajay

 

[anandvarma]

try this
=sum(--($a$6:$a$98="equity ops")*($b$6:$c$98=1))

 

[Comfy]

=sumproduct((a6:a98="equity ops")*(b6:b98=1))

 

[Peter_SSs]

Ajay

A couple of things that could be going wrong with your existing formula:

1. Are you certain that there are not stray spaces before or after any of the values?
eg If cell A8 appears to contain "EQUITY OPS" does =LEN(A8) in a vacant cell return 10?
Similar for the 1 values giving a LEN of 1?

2. Are you certain the 1 values are numbers, not text.
eg if cell B8 appears to contain a 1, does =ISNUMBER(B8) in a vacant cell return TRUE?

What version of Excel are you using?

 

[ajay_gajree]

Hi,

Thank you all for your answers, my numbers were indeed text, so I have now managed to resolve this thanks to you guys!

Best regards