Counting Unique text in column B based on a criteria from column a

**clocmasta** · 06-18-2013, 04:42 PM

Hello,

I've been beating my head in and reading every related thread I can on this issue. In my data dump I basically have a row containing 6 pieces of information, and I'm only concerned with two. I need a count of unique tasks performed by one resource. Column A would have a list of resource names, including duplicates as this is raw data dump, and column B would have a task performed by that person per line item. I'd like to be able to write a formula that said given this one person as criteria how many unique tasks did they perform? I've attached a sample spreadsheet but keep in mind the actual is tens of thousands of entries long and ever expanding so I can't make it a static table.

Unique List Sample.xlsx

**oeldere** · 06-18-2013, 05:28 PM

How about this solution with an pivot table?

**Teethless mama** · 06-18-2013, 06:05 PM

See the attached

**Tony Valko** · 06-18-2013, 06:40 PM

One way...

List the unique names in a range of cells, say E2:E5.

Then, enter this array formula** in F2 and copy down as needed:

=SUM(IF(FREQUENCY(IF(A$2:A$10=E2,MATCH(B$2:B$10,B$2:B$10,0)),ROW(B$2:B$10)-ROW(B$2)+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.

You can easily generate the list of unique names using advanced filter:

http://contextures.com/xladvfilter01.html#FilterUR

This method may be a bit slow to calculate on 10s of 1000s of rows of data.