# Count frequency based on person

#### RobbieL

##### Board Regular
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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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"

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

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

Hi,
Try in c2 =IF(A2=A1,"",COUNTIF(\$A\$2:\$A\$100,A2))

Hi,
Try in c2 =IF(A2=A1,"",COUNTIF(\$A\$2:\$A\$100,A2))

Thanks but unfortunately I cannot get that to work either.

Does this help display my issues:

Last edited:
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?

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...

Replies
65
Views
5K
Replies
0
Views
1K
Replies
1
Views
1K
Replies
1
Views
859
Replies
3
Views
2K

1,219,820
Messages
6,150,408
Members
450,960
Latest member
GB2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back