How to get the max value in a column based from distinct values in another column

Michael Giovanni
Michael Giovanni ✭
inFormulas and Functions

Hi mates. Can you please help me to get the max value in a column based from distinct values in another column?

For example is like this

image.png

如何找到“汽车”的最大价值基于勒ft table?

Please kindly help me, this is urgent.

Tags:
· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
If you set this as a column formula in your helper column, it will return the number of times each email is duplicated. You could also setup conditional formatting to highlight that row if the result is greater than 1.<\/p>

=COUNTIFS([Employee Email]:[Employee Email], [Employee Email]@row)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107559,"type":"question","name":"How do I check if two rows have the same contact name and if the start\/end dates overlap?","excerpt":"Assuming the check is for the row above.","snippet":"Assuming the check is for the row above.","categoryID":322,"dateInserted":"2023-07-13T14:55:20+00:00","dateUpdated":"2023-07-13T14:56:16+00:00","dateLastComment":"2023-07-13T15:53:29+00:00","insertUserID":163451,"insertUser":{"userID":163451,"name":"CuriousUser","url":"https:\/\/community.smartsheet.com\/profile\/CuriousUser","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T16:33:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163451,"lastUserID":163451,"lastUser":{"userID":163451,"name":"CuriousUser","url":"https:\/\/community.smartsheet.com\/profile\/CuriousUser","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T16:33:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":28,"score":null,"hot":3378526129,"url":"https:\/\/community.smartsheet.com\/discussion\/107559\/how-do-i-check-if-two-rows-have-the-same-contact-name-and-if-the-start-end-dates-overlap","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107559\/how-do-i-check-if-two-rows-have-the-same-contact-name-and-if-the-start-end-dates-overlap","format":"Rich","lastPost":{"discussionID":107559,"commentID":385043,"name":"Re: How do I check if two rows have the same contact name and if the start\/end dates overlap?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385043#Comment_385043","dateInserted":"2023-07-13T15:53:29+00:00","insertUserID":163451,"insertUser":{"userID":163451,"name":"CuriousUser","url":"https:\/\/community.smartsheet.com\/profile\/CuriousUser","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T16:33:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-13T15:52:43+00:00","dateAnswered":"2023-07-13T15:49:44+00:00","acceptedAnswers":[{"commentID":385040,"body":"

Hello!<\/p>

Would something like this work for you? The same formula for both contact and start date, except you change the reference to the column you want to check.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107558,"type":"question","name":"Remove an update request with a formula or workflow?","excerpt":"I have a sheet that will automatically request an update if certain parameters are met. But sometimes the fields get marked directly through the sheet instead of through the update request form. Is there a way to automatically remove the request update if the parameters are met through the sheet? For example if there is an…","snippet":"I have a sheet that will automatically request an update if certain parameters are met. But sometimes the fields get marked directly through the sheet instead of through the…","categoryID":322,"dateInserted":"2023-07-13T14:52:14+00:00","dateUpdated":null,"dateLastComment":"2023-07-13T14:55:56+00:00","insertUserID":131184,"insertUser":{"userID":131184,"name":"Chevon Brownell","url":"https:\/\/community.smartsheet.com\/profile\/Chevon%20Brownell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T14:52:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-13T15:28:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":12,"score":null,"hot":3378520690,"url":"https:\/\/community.smartsheet.com\/discussion\/107558\/remove-an-update-request-with-a-formula-or-workflow","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107558\/remove-an-update-request-with-a-formula-or-workflow","format":"Rich","tagIDs":[254,334,446,448],"lastPost":{"discussionID":107558,"commentID":385018,"name":"Re: Remove an update request with a formula or workflow?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385018#Comment_385018","dateInserted":"2023-07-13T14:55:56+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-13T15:28:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-13T14:57:37+00:00","dateAnswered":"2023-07-13T14:55:56+00:00","acceptedAnswers":[{"commentID":385018,"body":"

You are going to have to do it manually or the API may be able to manage that for you.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":446,"urlcode":"update-requests","name":"Update Requests"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions