Counting unique values with criteria

[Chuck]

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.

 

[RagDyer]

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)))

 

[Jim Thomlinson]

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)

 

[T. Valko]

Try this...

Data in the range A2:B6. Assumes no empty cells in the "Case" range.

D23 = 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.

 

[Chuck]

Thank you. That was helpful.