Counting unique values

**braydon16** · 02-18-2011, 06:20 PM

I have two columns of data that I need to analyze by counting the number of unique values.

A sample of the data would be in column A there are client names, and in column B there are tasks. I have a function that will give me the count even if a client is entered more than once. So, if the client has the same task done twice, I've been able to accomplish only counting it once. In this useless little example, it counts Bob - Rig as 1.

The formula for that was =SUMPRODUCT((A20:A50<>"")/COUNTIF(A20:A50,A20:A50&""))

Column A Column B
Bob Rig
Mark Call
Bob Rig
Bob Call
Mark Rig

My issue is, I'd like to count the number of unique items from column B. So, if I wanted to count the number of Rig, it would only be 2, as Bob Rig is in there twice.

Thanks!

**Ron Coderre** · 02-18-2011, 06:46 PM

Using your posted example...
and
D1: a Col_B value to count...eg Rig

This formula returns the count of unique Col_A values where the Col_B value matches D1

E1: =SUMPRODUCT((MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0)=(
ROW(A1:A10)-ROW(A1)+1))*(B1:B10=D1))

Using your example, the formula returns: 2

Is that something you can work with?

**braydon16** · 02-18-2011, 06:57 PM

It's certainly something I can work with. Thanks!

**braydon16** · 02-24-2011, 01:20 PM

If I have a third column that contains amounts, Column C, can I just show the maximium from the values from a specific criteria from Column A?

**Ron Coderre** · 02-24-2011, 04:31 PM

Just so we're clear...can you post a small example and what you want returned?

**braydon16** · 02-24-2011, 06:51 PM

Certainly. If I use the same example as above, but add an additional column with values...
Column A Column B Column C
Bob Rig 2
Mark Call 3
Bob Rig 2
Bob Call 1
Mark Rig 4

If I specify that I want to know the max spent on Rigs? Can it return Bob and the sum, 4? In other words, I'm trying to find the maximum of the unique values.

**daddylonglegs** · 02-24-2011, 08:06 PM

Assuming the same setup as Ron proposed then you could put this formula in F1 for the MAX spent on Rigs for a single person

=MAX(MMULT((A1:A10=TRANSPOSE(A1:A10))+0,(B1:B10=D1)*C1:C10))

and then in G1 for the person

=INDEX(A1:A10,MATCH(F1,MMULT((A1:A10=TRANSPOSE(A1:A10))+0,(B1:B10=D1)*C1:C10),0))

both formulas need to be confirmed with CTRL+SHIFT+ENTER

for the latter if there are ties it just retrieves the first name