# Excel 2013 Counting Rows per record

All,
I am working with a spreadsheet with ~60,000 rows.  Each row has data pertaining to one column (Column A).  I want to count the number of times a value in Column B is associated with a unique value in Column A.  And a value in Column B can appear multiple times per unique value in Column A; we only want to a Column B value one time per unique Column A value.

For instance:
Column A     Column B
New York      Bob
New York      Sue
New York      Steve
New York      Bob
Chicago        Mary
Chicago        Mary
Chicago        Mary

In Column C, we like to have the # of unique values of column B associated with each unique value of column A, so the result would look like this:

Column A     Column B   Column C
New York      Bob               3
New York      Sue
New York      Steve
New York      Bob
New York      Sue
Chicago        Mary              1
Chicago        Mary
Chicago        Mary

Can this be done in Excel?  And if it can, can someone tell us how?
Thanks!
###### Who is Participating?

x

Commented:
Since you have around 60,000 rows, perhaps using a helper column might improve efficiency over using array formulas....

Assuming your data is in columns A:B, then in C2 enter formula:

=COUNTIFS(A\$2:A2,A2,B\$2:B2,B2)

copied down.  (you can hide this column, if desired)

in D2 then:

=IF(A2=A1,"",COUNTIFS(A:A,A2,C:C,1))

copied down.
0

Commented:
Hi,

pls try this array formula

``````'=IF(A2=A1,"",SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$10=A2,MATCH(\$B\$2:\$B\$10,\$B\$2:\$B\$10,0)),ROW(\$A\$2:\$A\$10)-ROW(A2)+1),1)))
``````

array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER

Regards
ArrayFormula.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.