Multiple COUNT criteria, same row
 

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

Multiple COUNT criteria, same row
 

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 wrote:
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

Multiple COUNT criteria, same row
 

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


"pogiman" wrote:

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 wrote:
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

Multiple COUNT criteria, same row
 

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" wrote:
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

Multiple COUNT criteria, same row
 

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

"Teethless mama" wrote:

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" wrote:
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

Multiple COUNT criteria, same row
 

Hi Steve,

Bob Phillips discusses that in detail he

http://www.xldynamic.com/source/xld....CT.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

On Sep 2, 12:03*am, Steve Vincent
wrote:
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



"Teethless mama" wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -