Counting unique values with criteria
I need to count the number of unique cases given certain criteria. For
example, Case # Code 3356 Stroke 3356 Stroke 3357 Cancer 3357 Cancer 3356 Cancer The results should indicate: 1 unique case for Stroke; 2 unique cases for Cancer. Thanks for any help. |
Counting unique values with criteria
Enter the "Code" criteria in C1, then try this *array* formula:
=COUNT(1/FREQUENCY(IF((B2:B6=C1),MATCH(A2:A6,A2:A6,0)),ROW( 1:5))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Chuck" wrote in message ... I need to count the number of unique cases given certain criteria. For example, Case # Code 3356 Stroke 3356 Stroke 3357 Cancer 3357 Cancer 3356 Cancer The results should indicate: 1 unique case for Stroke; 2 unique cases for Cancer. Thanks for any help. |
Counting unique values with criteria
I would be inclined to do that with a pivot table. Check out this link on
counting unique in a pivot. http://www.contextures.com/xlPivot07.html#Unique here is how you do it... In column C add this formula =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,0,1) and copy down. Add a heading called Unique. Now create a pivot table off of that data. Add the Case to the left column and Unique to the data section... You could also just do a sumif formula at this point... =SUMIF(B2:B6, "=Cancer", C2:C6) -- HTH... Jim Thomlinson "Chuck" wrote: I need to count the number of unique cases given certain criteria. For example, Case # Code 3356 Stroke 3356 Stroke 3357 Cancer 3357 Cancer 3356 Cancer The results should indicate: 1 unique case for Stroke; 2 unique cases for Cancer. Thanks for any help. |
Counting unique values with criteria
Try this...
Data in the range A2:B6. Assumes no empty cells in the "Case" range. D2:D3 = stroke, cancer Enter this array formula** in E2 and copy down as needed: =SUM(IF(FREQUENCY(IF(B$2:B$6=D2,A$2:A$6),A$2:A$6), 1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Chuck" wrote in message ... I need to count the number of unique cases given certain criteria. For example, Case # Code 3356 Stroke 3356 Stroke 3357 Cancer 3357 Cancer 3356 Cancer The results should indicate: 1 unique case for Stroke; 2 unique cases for Cancer. Thanks for any help. |
Counting unique values with criteria
Thank you. That was helpful.
"Chuck" wrote: I need to count the number of unique cases given certain criteria. For example, Case # Code 3356 Stroke 3356 Stroke 3357 Cancer 3357 Cancer 3356 Cancer The results should indicate: 1 unique case for Stroke; 2 unique cases for Cancer. Thanks for any help. |
All times are GMT +1. The time now is 12:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com