CountIf for two columns
 

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

CountIf for two columns
 

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

CountIf for two columns
 

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

CountIf for two columns
 

SUMPRODUCT returns an array of True/False values. The -- is to coerce these
into 0/1 values that can be added up.

Could I suggest that you look at this previous NG posting where Ken Wright
gives a pretty thorough explanation

http://tinyurl.com/v85r

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Yakimoto" wrote in message
...
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

CountIf for two columns
 

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