Seeking a formula to identify repeat customers
I have a service metric to report how many repeat customers we have and I'm looking to build that via formula in a sheet summary. I have a Customer column that looks something like this:
Bob
Susan
Tim
Susan
Gina
Susan
Tim
If the formula works correctly it would return a result of of 2 as we have 2 repeat customers, Susan and Tim. I'm not looking to count the number of duplicate values, but rather to count the number of values that have duplicate entries.
I've tried several variations of COUNTIF, COUNTIFS, and IF statements to no avail. The AI formula generator hasn't been able to help either, unfortunately. I'd be thankful to anyone who can point me in the right direction!
Best Answer
-
Hi @skemp,
Are you able to modify the sheet structure? If so, one way is to use a "helper" column to track the number of times a customer name is used (this column can be hidden). Next, your summary field can get a count of all the names that appear multiple times.
The new column, which I called "Customer (Helper)" would have this formula:
=COUNTIF(Customer:Customer, =Customer@row)
The Summary field would then have this:
=COUNT(DISTINCT(COLLECT(Customer:Customer, [Customer (Helper)]:[Customer (Helper)], >1)))
Hope this helps,
Dave
Answers
-
Hi @skemp,
Are you able to modify the sheet structure? If so, one way is to use a "helper" column to track the number of times a customer name is used (this column can be hidden). Next, your summary field can get a count of all the names that appear multiple times.
The new column, which I called "Customer (Helper)" would have this formula:
=COUNTIF(Customer:Customer, =Customer@row)
The Summary field would then have this:
=COUNT(DISTINCT(COLLECT(Customer:Customer, [Customer (Helper)]:[Customer (Helper)], >1)))
Hope this helps,
Dave
-
@DKazatsky2 that worked perfectly! Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!