Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter Multiple Columns on Multiple Criteria and count the last co | Excel Worksheet Functions | |||
To count the data using multiple criteria in multiple columns | New Users to Excel | |||
Count multiple criteria | Excel Worksheet Functions | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions |