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.

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.

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.

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.

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.