first
On the Smartsheet forums, a user asked how to get the sum of values without duplicate rows. In this post, I will explain the answer to this question and his two methods to solve this problem.
Question summary
The user wanted to sum the values of COL B while excluding all but the first instance of the corresponding condition in COL A. May contain duplicate values. (The original Community article isplease use this form.. )
Answer summary
The best answer isRANKEQ functionor sending us a message on MATCH functionWe explained how to remove duplicate rows using .
Summary
The RANKEQ function is used to collect and rank row IDs that have the same COL A cell value. This ranking is then used to sum the COL B values that have a row ranking of 1.
The MATCH function is used to create a row number column and count the number of previous COL A cell values in the current row. This count can be used to filter out duplicate rows.
Both methods are useful for getting the sum of COL B values excluding duplicate rows.
Additional Information
- The RANKEQ function requires a unique range of numbers. If you don't have your own values, connections will be created. If you have other columns where cell values are unique, you can also use that.
- If your unique cell values are text rather than numbers, you can use the MATCH function to create a row number column to count the number of previous COL A cell values in the current row.
I hope this post was helpful.
Translation of correspondence
We need to sum the values of COL B while excluding all but the first instance of the corresponding condition in COL A. May contain duplicate values.
Example:
The result should be 19.
best answer
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Hello@MarianaBP
First you need to find out which rows are duplicates.
RANKEQ approach
Add an automatic numbering helper column, Row ID, and use the RANKEQ function.
=RANKEQ([row ID]@row, COLLECT([row ID]:[row ID], [COL A]:[COL A], [COL A]@row), 1)
This function aims to collect and rank row IDs that have the same COL A cell value. We then need to use this ranking to sum the COL B values that have a row ranking of 1.
https://app.smartsheet.com/b/publish?EQBCT=a645cb867c7142fa886ec4b2428515e6
match approach
The RANKEQ function requires a unique range of numbers to work in this situation. If you don't have your own values, connections will be created. If you have other columns where cell values are unique, you can also use that.
If your unique cell values are text rather than numbers, you can use the MATCH function to create a row number column to count the number of previous COL A cell values in the current row.
line number =MATCH([unique text]@row, [unique text]:[unique text], 0)
count =COUNTIFS([COL A]:[COL A], [COL A]@row, [row number]:[row number], <=[row number]@row)
answer
Matthew J. McAteer ✭✭✭✭
Hello@MarianaBP
Is there any reason why this table should have duplicate entries? Perhaps the way to solve this problem is to remove duplicates before summing the values. Can you tell us more about your specific use case?
Matthew
I gave a simple example above, but the sheet I'm working on contains several rows of tasks that may be linked to the same budget bucket. For example, tasks 1, 3, and 5 are assigned to the same budget, so the three tasks have the same budget value. At the end of the table, I would like to sum up the total budget, excluding duplicates.
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Hello@MarianaBP
First you need to find out which rows are duplicates.
RANKEQ approach
Add an automatic numbering helper column, Row ID, and use the RANKEQ function.
=RANKEQ([row ID]@row, COLLECT([row ID]:[row ID], [COL A]:[COL A], [COL A]@row), 1)
This function aims to collect and rank row IDs that have the same COL A cell value. We then need to use this ranking to sum the COL B values that have a row ranking of 1.
match approach
The RANKEQ function requires a unique range of numbers to work in this situation. If you don't have your own values, connections will be created. If you have other columns where cell values are unique, you can also use that.
If the unique cell values are text rather than numbers, you can use the MATCH function to create a row number column to count the number of previous COL A cell values in the current row.
line number =MATCH([unique text]@row, [unique text]:[unique text], 0)
count =COUNTIFS([COL A]:[COL A], [COL A]@row, [row number]:[row number], <=[row number]@row)
Thank you, it worked :)