CountIf for two columns

  • Thread starter Thread starter Yakimoto
  • Start date Start date
Y

Yakimoto

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
 
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))
 
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) ?
 
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)
 
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().
 
Back
Top