Multiple COUNT criteria, same row

[Steve Vincent]

I have searched the discussion group for answers, but none exactly match my
question. I have two columns, and I need to count how many times a certain
"match" occurs. E.g., here's the data:

A B
1 Abbott Knee replacement
2 Costello Knee replacement
3 Costello Shoulder replacement
4 Costello Shoulder replacement
5 Abbott Knee replacement

I need to count how many times Dr. Abbott did a knee replacement, how many
times Dr. Costello did a knee replacement, etc. Each query will reside in
its own cell, so I need a formula for each query. In the above example, I
would have four different cells containing the COUNT function/formula, e.g.,
one cell will be labeled "Abbott - Knee replacements", another will be
"Costello -- Knee replacements", another will be "Abbott -- shoulder
replacements", another will be "Costello -- shoulder replacements".

Any suggestions would be greatly appreciated. I have looked into DCOUNT,
etc., but most examples (that I have found) seem to be for occurrences not in
the same rows.

TIA,
Steve

 

[pogiman]

Hi Steve,

I'm new here and not an excel expert but this might help (I hope).

Try this:


A B C
D
1 Abbott Knee replacement =Concatenate($A1," - ",$B1)
=countif($C$1:$C$5,$C1)
2 Costello Knee replacement (copy formula to the
last record)
3 Costello Shoulder replacement
4 Costello Shoulder replacement
5 Abbott Knee replacement

Assuming $C$5 is your last record.

 

[Steve Vincent]

Pogiman,

Thank you, but I don't need to concatenate the two cells together, I need to
COUNT how many occurrences of Abbott/Knee replacement (e.g.) occur in that
range. I was just giving the four different permutations in the example,
with a label for each formula. But I think you for your reply!

Steve

 

[Teethless mama]

Criteria
C1: holds Abbott
C2: holds Costello
D1: holds Knee replacement
D2: holds Shoulder replacement


For Dr. Abbott did Knee replacement
In E1: SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))

For Dr. Abbott did Shoulder replacement
In E2: SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D2))

change C1 to C2 for Dr. Costello

 

[Steve Vincent]

Mama,

Thank you so much! That's exactly what I needed. Can you explain why the
two hyphens are necessary in the function? I tried entering the function
without the hyphens, and it didn't work...

Thanks again,

Steve

 

[Pete_UK]

Hi Steve,

Bob Phillips discusses that in detail here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#format

Basically, the double unary minus converts TRUEs and FALSEs to 1s and
0s, so they can be used in arithmetic operations. There are
alternatives to --, as Bob explains.

Hope this helps.

Pete