Return top 5 occurrances

I have a sheet to track customer requests (500+ rows), with a Salesperson column and customer column, the salesperson column is located farthest to the right of the sheet whereas the customer column is located on the 1st column. What I want to do is write a function that will reference a given Salesperson contact and be able to output the 1st most occurring customer, 2nd most occurring customer, etc. I want this to be a function so as entries get added throughout the year it will always grab the most occurring customer instead of manually looking through the sheet and writing a COUNTIFS formula looking for that manually entered customer.

Any help is appreciated, THANKS!

Find more posts tagged with

Sales

Manufacturing

Formulas

Project Management

Comments

Richard Rymill SBP

Hi TB

You can surface the stats you want in a series of reports using the filters and some well designed metrics in your sheets to pick up what you want to see. You can even view all this on a dashboard so everyone can see what is going on.

Sounds like an interesting workflow?

Hope that helps

Richardr@smarterbusinessprocesses.com

Paul Newcome

You could add a helper column (we'll call it the Count column) and use something along the lines of

=COUNTIFS([Customer Column]:[Customer Column], [Customer Column]@row, [Sales Rep Column]:[Sales Rep Column], [Sales Rep Column]@row)

This would give you the number of times that particular customer is in the same row as that particular sales rep. I would then suggest another helper column (called [Row ID]) where you enter something along the lines of

=COUNTIFS([Customer Column]$1:[Customer Column]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

This will essentially replicate your row number into something that can be used in formulas.

From there you would use these two columns as reference in a LARGE statement.

=LARGE(COLLECT(Count:Count, [Sales Rep Column]:[Sales Rep Column], "John Doe"), 1)

will return the highest number in the Count column for John Doe.

=INDEX(COLLECT([Row ID]:[Row ID], Count:Count, number_returned_by_LARGE_function, [Sales Rep Column]:[Sales Rep Column], "John Doe"))

will return the first row number where that number is housed.

=INDEX([Customer Column]:[Customer Column], number_returned_by_previous_INDEX_function)

will give you the customer name.

If you are able to provide some more details as to the layout, I would be happy to tweak the above to reflect more accurately what your setup is. Will the metrics be on the same sheet or different sheet?

James McCombs

Hey TB,

I agree with what Paul said above - I think adding in a helper column is going to be the best option; I'd title it something like "Total Orders". I'd then use this formula in the Total Orders column:

=COUNTIF([Customer Name]:[Customer Name], [Customer Name]1)

This will count all the orders by any given customer, and return a numeric value.

I'd probably recommend doing the most-frequent-customers calculations in a separate sheet, where the Primary Column has your Sales Rep names, and the columns are 1st, 2nd, 3rd, etc.for customer frequency.

The following formula will give you the Customer Name that appears nth most frequently, where N is the ranking (1 - most frequent, 2 - 2nd most, etc).

=INDEX(COLLECT([Customer Name]:[Customer Name], [Total Orders]:[Total Orders], LARGE([Total Completed]:[Total Completed], n)), 1)

If you're doing this in a separate sheet, you'll need to use the "reference sheet" option to tweak the formula above, so it should look something like this:

=INDEX(COLLECT({Customer Name}, {Salesperson}, $[Salesperson]1, {Total Orders}, LARGE({Total Orders}, n)), 1)

I think that should work to give you your rankings. You'll just need to make sure your sheet references are titled appropriately, and that "n" that is bolded and underlined above is replaced with a number to pull the customer with that rank position.

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…