SUMPRODUCT expects numeric values. (A1:A1000=D1) returns an array of
boolean (TRUE/FALSE) values, which would be ignored. Boolean values
are coerced by XL to 1 (TRUE) and 0 (FALSE) in math formulas, so
putting a "unary minus operator" (negative sign) in front of the
quantity converts the array to numbers (i.e, -1 for TRUE and 0 for
FALSE). The second unary minus converts the -1s to positive values.

These arrays are then multiplies and summed by SUMPRODUCT().


In article ,
"Yakimoto" wrote:

Thanks J.E., this is exactly what I needed.
Could you tell me what are these two minuses for in the formula
(--(A1:A1000=D1) ?

"J.E. McGimpsey" wrote in message
...
One way:

Assume your columns are A & B. Further assume your criteria are in
D1 (for column A) and E1 (for column B):

=SUMPRODUCT(--(A1:A1000=D1),--(B1:B1000=E1))


In article ,
"Yakimoto" wrote:

I have two columns populated with different values.
I would like to count all the cells pair having specific values, i.e.
like
countif function, but for both cells.
For now I am doing this concatenating both columns and after that
performing
CountIf over resulting column.
Another way is to write a piece of code looping and counting, but it
seems
too much for such a simple thing.
Is there any other way of doing that? Without intermediate steps, only
using
one formula?

Thanks,
Yakimo