# How to get distinct values from a column, then shows their maximum number?

Options

How to create a formula that can get me the distinct name list from the left table then get their max values?

«1

• ✭✭✭✭✭✭
Options

Put this formula into each cell in Max Number column

=MAX(COLLECT(Number:Number, [List of Vehicles]:[List of Vehicles], [Distinct name]@row))

• ✭✭✭✭✭
edited 06/29/20
Options

@Frank Falco Thank you for the formula! As an add on, I converted MAX to SUM, and added an IF logic as a prefix, to only add totals where the Distinct Name was completed in the summary table

Works perfectly!

• Options

I'm using the first helper column to create numbers for distinct entries in a column, It does well for anything that has text in front of it, but skips rows with # only that are unique. Is there a way to fix this to grab those as well?

For example SWFZ160493 will get a # , but 111152 will be skipped. It is showing as a string and not a value.

Thanks,

Jeff

• Options

Hi There,

I can't get it work.

What is wrong with my formula?

=IF(COUNTIFS(Description2045:Description2054, Description@row) = 1, COUNT(DISTINCT(Description2045:Description2054)))

• ✭✭✭✭✭✭
Options

@Jeff Bunker Create a text/number column and then use this as a column formula:

=[Column name]@row

Change [Column Name] to reflect whichever column contains "SWFZ160493" and "111152".

Then reference this new column in your formula that generates your numbers.

The problem you are running into is that your data is not all the same format. You have some numbers and some text. All data values must be the same. Using this helper column converts everything to a text string and should get the job done for you.

• ✭✭✭✭✭✭
Options

@Juraj Slezak Are you getting an error or an incorrect result? What exactly are you trying to accomplish with your formula?

• Options

@Paul Newcome this has greatly helped me towards achieving a sheet listing distinct values (customer names in my example). I have adapted your example to show the SUM not MAX . My challenge is that my master sheet has to be populated by smartsheet 'Forms' hence I the 'help column' or 'Number column' for me, cannot ne prepopulated manually as you describe "

I have tried to make the properties of my 'Number' help column as Auto-number, but sadly that stops the formula from creating the distinct /unique value (customer name).

• ✭✭✭✭✭✭
Options

@Andy Trickett Are you able to move the distinct list starting with the Number column to a separate sheet? That would allow you to prefill as many rows as you need to.

• Options

@Paul Newcome Yes, a really helpful nudge in the right direction thank you. I have created a new sheet solely to hold my growing 'DISTINCT' list, alongside my 500 rows of prepopulated consecutive NUMBERS (help column) Which I actually used the sheets PIMARY COLUMN for. I left the COUNT (help column) in the 'Master sheet' which has the smartsheets Form feeding it. All work great & KPIs shown in a Dashboard. 😀 thanks again.

• ✭✭✭✭✭✭
Options
• Options

Are there any limitations on the "distinct" formula? I'm using it in smartsheets and seems to stop pulling in distinct data after row 273

• ✭✭✭✭✭✭
Options

@Veronika Young It depends. How exactly are you using it?

• Options

@Paul Newcome it turned out to be a limitation of 4,000 characters in the cell, not an issue with the formula.

• ✭✭✭✭✭✭
Options

@Veronika Young That was going to be my first guess. I am glad you got it figured out. Were you able to implement a solution?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!