Need help with formula to weed out duplicates

[Meenie]

Excel 2003
I receive information regarding patients in a hospital setting, the patient
being
identified by Medical Record Number.
I need to know the number of pts on ventilators in a month, then the
compliance
to several different tasks/processes performed on those patients per month.
(# of yes answers divided by the total number of pts) The problem is that a
lot of the data received is on the same pt, repeated on different days.
I have a formula to disregard the duplicate med rec numbers:
=sumproduct((range<>"")/countif(range,range&""))
this works fine to count the number of patients for the month without
counting duplicates.
How can I count the number of yes/no answers in other columns without
counting the duplicates? I don't know how to put that together successfully.
thanks, Meenie

 

[T. Valko]

It's not real clear what you want to do.

Do you want to count Yes and No for unique patients?

P1...Y
P1...Y
P2...N
P2...Y
P3...N
P4...Y
P4...Y

Where unique "Yes" would = 3 and unique "No" would = 2

 

[Meenie]

Yes, that's what I want to do.
I have the med rec numbers in column B and the yes no answers in several
other columns (would only calculate for one column at a time) but not sure
how to make a formula that looks at the med rec number, dismisses duplicates,
and counts the "yes" answers in another column.
Is that do-able?

 

[T. Valko]

Try these array formulas** :

Based on the sample data I posted. Assumes no empty cells in the patient ID
column.

For unique Yes

=SUM(IF(FREQUENCY(IF(B2:B8="Yes",MATCH(A2:A8,A2:A8,0)),ROW(A2:A8)-ROW(A2)+1),1))

For unique No:

=SUM(IF(FREQUENCY(IF(B2:B8="No",MATCH(A2:A8,A2:A8,0)),ROW(A2:A8)-ROW(A2)+1),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.

 

[Meenie]

Thanks!! That works great. I'm lost when I get to array formulas. Maybe I'll
get there someday (sigh)
Meenie