A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

count how many times same names appear in a column



 
 
Thread Tools Display Modes
  #1  
Old March 12th 10, 05:05 PM posted to microsoft.public.excel.misc
hp
external usenet poster
 
Posts: 13
Default count how many times same names appear in a column

I have a column of names with patients who has blood transfusions . For
example Paul Smith with appear 3 times ie he had 3 units of blood transfusion.
What I need is total number of patients who has blood transfusion. The list
is about 1400 cells long.
Can anyone help please?
Ads
  #2  
Old March 12th 10, 05:20 PM posted to microsoft.public.excel.misc
Luke M[_4_]
external usenet poster
 
Posts: 457
Default count how many times same names appear in a column

Check out the XL help file article:
"Count unique values among duplicates "

You can do this via filters or functions, your choice. For your example, it
looks like you will need something like:
=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))

Note that this is an array formula, and must be confirmed using
Ctrl+Shift+Enter, not just Enter.

--
Best Regards,

Luke M
"HP" > wrote in message
...
>I have a column of names with patients who has blood transfusions . For
> example Paul Smith with appear 3 times ie he had 3 units of blood
> transfusion.
> What I need is total number of patients who has blood transfusion. The
> list
> is about 1400 cells long.
> Can anyone help please?



  #3  
Old March 12th 10, 05:27 PM posted to microsoft.public.excel.misc
MDBCT
external usenet poster
 
Posts: 2
Default count how many times same names appear in a column

Use the following array formula:
=sum(1/Countif(RangeOfCells,RangeOfCells))

confirm the formula with Ctrl +Shift+Enter instead of just the Enter key

"HP" wrote:

> I have a column of names with patients who has blood transfusions . For
> example Paul Smith with appear 3 times ie he had 3 units of blood transfusion.
> What I need is total number of patients who has blood transfusion. The list
> is about 1400 cells long.
> Can anyone help please?

  #4  
Old March 12th 10, 05:30 PM posted to microsoft.public.excel.misc
MDBCT
external usenet poster
 
Posts: 2
Default count how many times same names appear in a column

Use the following Array formula (confirm formula with Ctrl+Shift+Enter)
=sum(1/countif(RangeOfCells,Range,OfCells))



"HP" wrote:

> I have a column of names with patients who has blood transfusions . For
> example Paul Smith with appear 3 times ie he had 3 units of blood transfusion.
> What I need is total number of patients who has blood transfusion. The list
> is about 1400 cells long.
> Can anyone help please?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count the times a value appears in a column based on another value TrainingGuru Excel Worksheet Functions 6 January 6th 09 05:43 AM
Formula to enter names in a column specific # of times Steve Excel Worksheet Functions 10 August 25th 08 04:21 AM
How can I count how many times different texts repeat in a column? pjr New Users to Excel 13 May 30th 08 04:58 AM
Count how many times the same word appears in column Alexa Excel Discussion (Misc queries) 5 April 16th 05 03:59 PM
How do I count how many times x appears in a column? Ian Roberts Excel Worksheet Functions 2 November 7th 04 03:13 PM


All times are GMT +1. The time now is 02:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.