CODEWITHSUNDEEP
excelexcel-formula
user2525645
user2525645

Reputation: 73

How to count the number of times a client is a repeat customer in excel

enter image description here

If you look at the example above, I'm trying to count the number of times a customer has been a repeat buyer. Each row is an item that was purchased and there might be multiple items in a transaction therefore multiple rows for a transaction (trxn #). I only want them to be counted as a repeat customer if they have multiple unique transactions and I want the last row there to be a tally of the number of times they've been repeat buyers. The spreadsheet is ordered by the transaction date and the items in a transaction are always grouped sequentially as seen by the first 2 rows which are two items in the same transaction.

Upvotes: 0

Views: 11105

Answers (2)

ARich
ARich

Reputation: 3279

You can accomplish this with a Pivot Table, although you may prefer the formula option better.

enter image description here

Just use a Vlookup formula to find the number of instances for each client:

=Vlookup(B2,F:G,2,FALSE)

The pivot is set up like this:

enter image description here

Upvotes: 0

Jerry
Jerry

Reputation: 71578

Maybe something like that?

=IF(COUNTIF($I$2:I2,I2)=1,1,IF(COUNTIFS($I$2:I2,I2,$H$2:H2,H2)>1,OFFSET($I$2,MATCH(H2&I2,$H$2:H2&$I$2:I2,0)-1,1),MAX(IF($I1:I$2=I2,$J1:J$2))+1))

Not sure if it can be made shorter but that's working on my test worksheet.

COUNTIF($I$2:I2,I2)=1 just checks if it's the first time the formula sees the name.

COUNTIFS($I$2:I2,I2,$H$2:H2,H2)>1 checks to see if there are multiple items in a transaction by using the date to identify them.

If there are more than one item bought on the same date, then OFFSET($I$2,MATCH(H2&I2,$H$2:H2&$I$2:I2,0)-1,1) gets the count number of the previous item bought within the same transaction.

Otherwise, if it is the first item bought in that transaction, MAX(IF($I1:I$2=I2,$J1:J$2))+1 finds the current count of the previous transaction and adds one.

It's an array formula though and you'll need to press Ctrl+Shift+Enter for it to work properly.

Upvotes: 1

Related Questions