Count frequency based on person

[RobbieL]

I've been googling this all morning but with no joy so thought it made sense to ask the learned minds on here!

I have a table of data which contains:

the unique customer ref in column A (replicated across rows if there is more than one transaction),
the individual transaction ref in column B.

I need to know how many individual transactions have taken place for each individual. I'm sure there's a simple way to do this using frequency and an array formula but evidently I haven't had enough coffee yet!

Grateful if someone could point me in the right direction.

Many thanks

 

[etaf]

would there be the same "individual transaction ref" in more than one row - OR does each row represent a unique transaction
if so then a CountIF() should work and just count the "unique customer ref"

 

[Special-K99]

Sounds fairly simple.
Can you provide a diagram, or dummy spreadsheet as I cant quite visualize the data.

 

[RobbieL]

would there be the same "individual transaction ref" in more than one row - OR does each row represent a unique transaction
if so then a CountIF() should work and just count the "unique customer ref"

No, each row represents a different unique transaction ref but the customer ref will stay the same (as it's a new transaction for the same person). So I want it to display:

Column A Column B Count Unique Transactions
1234 9876 2
1234 8765
0001 4563 3
0001 9745
0001 3122

 

[cyrilbrd]

Hi,
Try in c2 =IF(A2=A1,"",COUNTIF($A$2:$A$100,A2))
Adjust range to fit your model.

 

[RobbieL]

Hi,
Try in c2 =IF(A2=A1,"",COUNTIF($A$2:$A$100,A2))
Adjust range to fit your model.

Thanks but unfortunately I cannot get that to work either.

Does this help display my issues:

 

[cyrilbrd]

Thanks but unfortunately I cannot get that to work either.

Does this help display my issues:

What version of excel are you using?
What does not work exactly?
Based on model posted in thread #1 what fails?

 

[cyrilbrd]

And while waiting for more info, have you tried to extract your summary with a pivot table? Based on your first data you are looking solely at ColumnA, a pivot could count the occurrences of customer reference and return an exact count...