countif in 2 columns

  • Thread starter Thread starter Tonso
  • Start date Start date
T

Tonso

Using XL2002 for Window, I need to count the number of items that
match criteria in both of 2 columns. Column A contains a list of
numbers that can range from 1-12, corresponding to the month, and
column D has an individuals initials. I want to be able to determine,
for example, how many entries would match "1" in column A and "JC" in
column D. Is there a function that will do that?

Thanks, Tonso
 
Use SUMPRODUCT.

=SUMPRODUCT(--(A1:A100=1),--(B1:B100="JC"))

HTH,
Elkar






- Show quoted text -
Click to expand...

Thanks so much Elkar! Did just what i wanted it to do. My only
question is...how? For example, what does "--" signify?
Thanks again!
Tonso
 
SUMPRODUCT works by multiplying each of the arguments, then adding the
results for each value in the range. It only works on numbers, however.
Since A1:A100=1 returns either TRUE or FALSE, we need to convert this to a
number so SUMPRODUCT can use it. The -- effectively multiplies the TRUE (1)
or FALSE (0) by -1 one twice. Thus resulting in 1 or 0. (This may seem
redundant, but while Excel recognizes TRUE as 1 and FALSE as 0, SUMPRODUCT
doesn't)

So, let's use the following data set as an example:

A B
1 1 AJ
2 1 JC
3 2 JC

Using the SUMPRODUCT formula:
A1 is TRUE and B1 is FALSE, therefore 1x0=0.
A2 is TRUE and B1 is TRUE, therefore 1x1=1.
A3 is FALSE and B1 is TRUE, therefore 0x1=0.

Then, add the results together, 0+1+0=1. So, one data set meets both
criteria.

HTH,
Elkar
 
SUMPRODUCT works by multiplying each of the arguments, then adding the
results for each value in the range. It only works on numbers, however.
Since A1:A100=1 returns either TRUE or FALSE, we need to convert this to a
number so SUMPRODUCT can use it. The -- effectively multiplies the TRUE (1)
or FALSE (0) by -1 one twice. Thus resulting in 1 or 0. (This may seem
redundant, but while Excel recognizes TRUE as 1 and FALSE as 0, SUMPRODUCT
doesn't)

So, let's use the following data set as an example:

A B
1 1 AJ
2 1 JC
3 2 JC

Using the SUMPRODUCT formula:
A1 is TRUE and B1 is FALSE, therefore 1x0=0.
A2 is TRUE and B1 is TRUE, therefore 1x1=1.
A3 is FALSE and B1 is TRUE, therefore 0x1=0.

Then, add the results together, 0+1+0=1. So, one data set meets both
criteria.

HTH,
Elkar






- Show quoted text -
Click to expand...

Thanks so much for the explanation. It helped me understand what was
happening, and i will save it for future reference, as this seems to
be a very useful techique!
Tonso
 
Back
Top