How to create a top five.

Javier

Hi!

I've working on a dataset for a while now that essencially logs the everyday orders we receive from our clients.

One of the colums records the name of the customer and I've been trying to create a report that counts how many orders a customer has requested and sort them dynamicaly as a top five/ten. It's quite easy to do on other tools like Excel through pivot tables, but sadly that functionality is locked in SmartSheet as a premium feature that would be simply hell to try and convince the company to pay the licencing fee for.

I was wondering if someone here knows of a workaround or a function that allows to do such a basic filter for the data.

Thank you.

Chris Mondeau

Sure thing! I added in a new date range {Date} which references the Order Date column. I also created 2 new columns, Monthly Orders and Monthly Sales. This filter the range based on the current year and month from the today() formula to keep your metrics dynamic.

Chris Mondeau

Hi Javier,

You might be able to do this with a second sheet that contains a reference of your customer names. From there you can count all orders and sum all orders that match the customer name, then put a rank formula for each to rank the customer based on either the total number of orders made, or the total amount of sales.

Here's the orders table:

The {Customer} range is a cross sheet reference to the "Customer" column on the orders table, and the {Amount" is the amount column on the orders table as well.

And here's the Customer Metrics Table (counting all orders)

Customer Table summing all sales:

And the Ranking of Sales:

Javier

Thanks a lot, it's a great insight towards the solution to my problem.

=COUNTIF({Rango 1}; [Costumer Name]1) = Returns a count on all the orders placed by each client.

=RANKEQ([Total Jobs]1; $[Total Jobs]$1:$[Total Jobs]$12) = Returns a ranking on all the clients according to the previous results.

But now another issue appeared, I've been trying to filter the search in order to narrow the data to a certain time lapse based on the column date of the orders (current month), so that we could easily filter the data on a report, I've tried both with COUNTIFS and a combination of COUNT and AND, but so far the formula only returns the amount of days in which each client has placed an order (not the number of orders themselves).

=COUNTIFS({Rango 2}; <TODAY(-30); {Rango 1}; [Costumer Name]1)

Which is the error?

Chris Mondeau

Sure thing! I added in a new date range {Date} which references the Order Date column. I also created 2 new columns, Monthly Orders and Monthly Sales. This filter the range based on the current year and month from the today() formula to keep your metrics dynamic.

Javier

Thanks @cmondo !, the thing is working as expected, thank you again for your time and disposition.

Clare Coyne

Hi,

I am trying to rank particular cells based on the wording in my Turnover Band column

My formula is returning Unparseable.

Can you please please assist?

Neil Watson

@Clare Coyne you have left the 2nd " off the formula:

and the blue ) after that should also be deleted.

Neil Watson

Hi @Clare Coyne I had another look and I don't think the way you have set this up, that the RANKEQ will work since it looks at the entire list and not only the turnover band you are wanting...see below:

Even with the corrected formula, the ranking is for the entire column's worth of data.

Can you rethink the sheet structure so that the turnover bands are in different columns? That way you can use the RANKEQ on that.