Formula to count how many numbers repeat in a column.

**simonheller** · 11-26-2010, 09:03 PM

My spreadsheet records admissions/discharges on a hospital floor. Each admission and its corresponding discharge are recorded in 1 row. The unique hospital card number of each patient is admitted in Column D (number can be anywhere from 4 to 8 digits).

What formula will yield the number of hospital card numbers that are repeated in Column D (the number of patients who have been admitted more than once)?

If Column D contains:
4444
4444
7777777
55555
4444
666666
88888888
7777777
then the formula should yield "2" (because the 2 numbers "4444" and "7777777" are repeated).

I think this is complicated enough for now. Eventually, though, I want to have cells that show how many patients have been admitted 2 times; how many have been admitted 3 times; etc. Hopefully I will be able to figure out those formulae on my own, referring to the cell I am asking about here.

Thanks in advance for any suggestions/advice,

- Simon

**KiPA** · 11-26-2010, 09:38 PM

Well here's a harsh solution with one accessory column used. Result in B3.

Edit: The round seems to be pretty useless. Just recalled memories of some ancient problems with 1/3, 1/6 etc. values with endless line of decimals.

**KiPA** · 11-26-2010, 09:50 PM

This might also work with the how-many-times problem:

2 times: =COUNTIF(F:F;1/2)/2
3 times: =COUNTIF(F:F;1/3)/3
4 times: =COUNTIF(F:F;1/4)/4

etc...

**Marcol** · 11-26-2010, 10:09 PM

Assuming your data is not sorted on column D and begins in row 2

In a spare column in row2

Please Login or Register  to view this content.

Drag/ Fill down

Then filter this column for the number of repeats you are interested in.

If you can sort the data in column D

Please Login or Register  to view this content.

Hope this helps

**DonkeyOte** · 11-27-2010, 04:12 AM

In terms of single cell:

Please Login or Register  to view this content.

I would suggest however you use a Pivot for this.
Set Patient Card Number as both Row Label and Data Field (set to Count) and then order the results accordingly
(this will give you a listing of patients and associated frequency)

**zbor** · 11-27-2010, 05:10 AM

This will also work but for exact range:

=SUMPRODUCT(--(COUNTIF(D1:D8,D1:D8)>1),1/COUNTIF(D1:D8,D1:D8))

**DonkeyOte** · 11-27-2010, 07:07 AM

@zbor... that's a pretty calculation intensive approach

technically you could shorten based on the fact that the 2 separate arrays represent both sides of the division:

Please Login or Register  to view this content.

@simonheller, in terms of suggestions thus far ranked in order of my own entirely subjective order of preference:

1 - Pivot Table
2 - Use Helpers thereby making all subsequent calculations trivial
3 - Use Basic Array (ie Frequency)
4 - Use SUMPRODUCT

**simonheller** · 12-07-2010, 06:43 PM

Thanks Marcol!

Your formula =IF(D2=D3,"",IF(COUNTIF(D:D,D2)=1,"",COUNTIF(D:D,D2))) worked beautifully.

I created a new column and put that formula in row 2 of it, then filled down to all the other rows. A number now appears in the row in which the most recent admission is recorded (but not if it is only the patient's first admission). It can be sorted with no problem too!

And thanks for figuring out that
"The unique hospital card number of each patient is admitted in Column D",
was really supposed to read
"The unique hospital card number of each patient admitted is recorded in Column D".

I also created one-off cells that tell how many people have been admitted twice, three times, etc.:
=COUNTIF(C2:C800,2), =COUNTIF(C2:C800,3), etc.