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
|