CountIF This=? AND This=?

[vischo]

I would like to count the cells that match two criteria.
How might I use a COUNTIF function.

Count cells in a row A if A1:A10 = "John" AND B1:B10 = "Yes".

A B
John No
John Yes
John Yes
Sue No
Sue Yes


Should equal two.

Thanks

 

[vtisix]

Try:

=sumproduct(--(A1:A10="John"),--(B1:B10="Yes"))

 

[vischo]

Works exactly.

Could you also explain the syntax of the function, I haven't seen i
used that way before and the "--"?

Thanks

 

[vtisix]

Basically, the -- (unary minus) coerces the Boolean values of True/False
to 1/0. See this link for a more extensive discussion.

http://www.excelforum.com/printthread.php?s=&threadid=197781