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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…