Prev: Excel Conditional Formatting for Percentagrs
Next: Too Many Worksheets
From: HP on 12 Mar 2010 12:05
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?
From: Luke M on 12 Mar 2010 12:20
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" <HP(a)discussions.microsoft.com> wrote in message
news:4D94A728-7F4E-4193-99F2-9DD1FCED91A9(a)microsoft.com...
>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?


From: MDBCT on 12 Mar 2010 12:27
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?
From: MDBCT on 12 Mar 2010 12:30
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?
 | 
Pages: 1
Prev: Excel Conditional Formatting for Percentagrs
Next: Too Many Worksheets