Count only once with multiple columns
 

I have two columns:
A B
Cancelled ABC Company
Cancelled ABC Company
Active ABC Company
Cancelled ABC Company
Cancelled XYZ Company
Cancelled John Doe Company
Active The Smith Company

So I want to return this:
Cancelled 3
Active 2
Meaning that
there are 3 unique values counted once that are "Cancelled"
there are 2 unique values counted once that are "Active"

Thanks in advance

Count only once with multiple columns
 

Try the following to get your Active Counts:

=COUNTIF(A2:A8,"Active")

Use this for your cancelled counts:

=COUNTIF(A2:A8,"Cancelled")

"jhicsupt" wrote:

I have two columns:
A B
Cancelled ABC Company
Cancelled ABC Company
Active ABC Company
Cancelled ABC Company
Cancelled XYZ Company
Cancelled John Doe Company
Active The Smith Company

So I want to return this:
Cancelled 3
Active 2
Meaning that
there are 3 unique values counted once that are "Cancelled"
there are 2 unique values counted once that are "Active"

Thanks in advance

Count only once with multiple columns
 

Data in the range A2:B8. Assuming no empty cells within the range.

E2 = Cancelled
E3 = Active

Enter this array formula** in F2 and copy down to F3:

=SUM(--(FREQUENCY(IF(A$2:A$8=E2,MATCH(B$2:B$8,B$2:B$8,0)) ,ROW(B$2:B$8)-ROW(B$2)+1)0))

** 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


"jhicsupt" wrote in message
...
I have two columns:
A B
Cancelled ABC Company
Cancelled ABC Company
Active ABC Company
Cancelled ABC Company
Cancelled XYZ Company
Cancelled John Doe Company
Active The Smith Company

So I want to return this:
Cancelled 3
Active 2
Meaning that
there are 3 unique values counted once that are "Cancelled"
there are 2 unique values counted once that are "Active"

Thanks in advance

Count only once with multiple columns
 

Thanks so much!!! This worked like a charm!

"T. Valko" wrote:

Data in the range A2:B8. Assuming no empty cells within the range.

E2 = Cancelled
E3 = Active

Enter this array formula** in F2 and copy down to F3:

=SUM(--(FREQUENCY(IF(A$2:A$8=E2,MATCH(B$2:B$8,B$2:B$8,0)) ,ROW(B$2:B$8)-ROW(B$2)+1)0))

** 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


"jhicsupt" wrote in message
...
I have two columns:
A B
Cancelled ABC Company
Cancelled ABC Company
Active ABC Company
Cancelled ABC Company
Cancelled XYZ Company
Cancelled John Doe Company
Active The Smith Company

So I want to return this:
Cancelled 3
Active 2
Meaning that
there are 3 unique values counted once that are "Cancelled"
there are 2 unique values counted once that are "Active"

Thanks in advance

Count only once with multiple columns
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jhicsupt" wrote in message
...
Thanks so much!!! This worked like a charm!

"T. Valko" wrote:

Data in the range A2:B8. Assuming no empty cells within the range.

E2 = Cancelled
E3 = Active

Enter this array formula** in F2 and copy down to F3:

=SUM(--(FREQUENCY(IF(A$2:A$8=E2,MATCH(B$2:B$8,B$2:B$8,0)) ,ROW(B$2:B$8)-ROW(B$2)+1)0))

** 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


"jhicsupt" wrote in message
...
I have two columns:
A B
Cancelled ABC Company
Cancelled ABC Company
Active ABC Company
Cancelled ABC Company
Cancelled XYZ Company
Cancelled John Doe Company
Active The Smith Company

So I want to return this:
Cancelled 3
Active 2
Meaning that
there are 3 unique values counted once that are "Cancelled"
there are 2 unique values counted once that are "Active"

Thanks in advance