Advanced counting

I want to count how many cells that have a value given that a corresponding cell in another column has the value x. Any good ideas how to get this done? The开发者_运维知识库 value given if one wanted to count how many cells in A has a value given that the corresponding cell in B has an x on the dataset on the image under is 4

=SUMPRODUCT(NOT(ISBLANK(A1:A12))*(B1:B12="x"))

This resolves as

=SUMPRODUCT(NOT({FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})*(B1:B12="x"))

=SUMPRODUCT({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}*(B1:B12="x"))

=SUMPRODUCT({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}*{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE})

=SUMPRODUCT({1;0;1;0;0;0;1;0;1;0;0;0})

Trues are 1 and Falses are zero when forced to be a number (like when you multiply them).

Try:

SUMPRODUCT(IF(ISBLANK($A$1:$A$12);0;1)*IF($B$1:$B$12="x";1;0))

You may have to validate like an array formula with CTRL-SHIFT-ENTER