Count only once with multiple columns

  • Thread starter Thread starter jhicsupt
  • Start date Start date
J

jhicsupt

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
 
Try the following to get your Active Counts:

=COUNTIF(A2:A8,"Active")

Use this for your cancelled counts:

=COUNTIF(A2:A8,"Cancelled")
 
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.
 
Thanks so much!!! This worked like a charm!

T. Valko said:
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.
Click to expand...
 
Back
Top