Sum column with duplicate data

[dr772]

I have a spreadsheet with patients and need to sum the number of patients but some patients may be entered multiple times.See below

A
Smith
Jones
SMith
Michaels
Rogers
Smith

I need my result to be 4 not 6

 

[xlhelp]

=SUM(1/COUNTIF(A1:A7,A1:A7))-1

and enter it as an array formula. That is, use CNTRL-SHFT-ENTER rather than just ENTER


Member- AAAA Association Against Acronym Abusers

 

[dr772]

i USED THIS WHICH WORKED BUT i NEED TO ALSO SUM BY SPECIFIC DATE
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

a B
SMITH 1/13/06
JONES 1/13/06
ROSS 2/10/06
SMITH 2/10/06
SMITH 2/10/06

I need for 1/13 = 2 and 2/10=2

 

[MallardVHS]

Is this a PivotTable/Count sort of deal? I'm unsure (as I sit here right now at the moment...) what impact Capitalization has on the issue.

Good luck, anyway!

Don

[green]Tis far easier to keep your duck in a row if you just have the one.[/green]

 

[KenWright]

Take a look at Pivot Tables, as they will handle this kind of data easily.

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Capitalisation shouldn't be an issue, but any differences in the form of leading/trailing spaces would be, but that would also affect any other method unless you specifically cater for it.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Welshbird]

DR772: Are you trying to count 'unique's' for a specific date? If so, I have a method I usually use for doing this - doesn't generally take too long.... let me know.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[dr772]

Fee

Yes that is what I need to do...please let me know your method...
Thanks