countif in 2 columns

[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

 

[Guest]

Use SUMPRODUCT.

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

HTH,
Elkar

 

[Tonso]

Use SUMPRODUCT.

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

HTH,
Elkar






- Show quoted text -

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

 

[Guest]

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

 

[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






- Show quoted text -

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