Discounting duplicates
 

Is there a way in which I can count cells from particular columns which
contain certain strings but to not count duplicates?

Example

A B
1 Patient number Status
2 1234 Unwell
3 5678 Well
4 9876 Well
5 1234 Unwell
6 9234 Well
7 3456 Unwell

In the above example I want to count the number of unwell patients. However
one of the patients (number 1234 in column A) is duplicated. Therefore, the
number of unwell patients is actually 2, not 3 as would be the answer if I
simply counted the number of unwell entries in column B

Thanks

Discounting duplicates
 

Select the cells in columns A & B and:

Data Filter Advanced Filter specify uniques and put the result in
column C & D

then =COUNTIF(D1:D100,"Unwell")
--
Gary''s Student - gsnu200909


"JRD" wrote:

Is there a way in which I can count cells from particular columns which
contain certain strings but to not count duplicates?

Example

A B
1 Patient number Status
2 1234 Unwell
3 5678 Well
4 9876 Well
5 1234 Unwell
6 9234 Well
7 3456 Unwell

In the above example I want to count the number of unwell patients. However
one of the patients (number 1234 in column A) is duplicated. Therefore, the
number of unwell patients is actually 2, not 3 as would be the answer if I
simply counted the number of unwell entries in column B

Thanks

Discounting duplicates
 

Assuming that the patient numbers really are integer numbers and there are
no empty cells in the patient number column...

Array entered** :

=SUM(IF(FREQUENCY(IF(B2:B7="unwell",A2:A7),A2:A7), 1))

Or, this generic array formula** with the same caveat on empty cells:

=SUM(IF(FREQUENCY(IF(B2:B7="unwell",MATCH(A2:A7,A2 :A7,0)),ROW(A2:A7)-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.

--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
Is there a way in which I can count cells from particular columns which
contain certain strings but to not count duplicates?

Example

A B
1 Patient number Status
2 1234 Unwell
3 5678 Well
4 9876 Well
5 1234 Unwell
6 9234 Well
7 3456 Unwell

In the above example I want to count the number of unwell patients.
However
one of the patients (number 1234 in column A) is duplicated. Therefore,
the
number of unwell patients is actually 2, not 3 as would be the answer if I
simply counted the number of unwell entries in column B

Thanks