Sum of values excluding rows with duplicate

Mariana B P · 12/21/23

I need to sum the values in COL B while excluding all but the first instance of the corresponding criteria in COL A, which may contain duplicate values.

Example:

The result should be 19.

jmyzk_cloudsmart_jp · 12/24/23

Hi @Mariana B P

First, you need to find which row is a duplicate.

RANKEQ approach

I would add an auto number 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)

The function is meant to collect Row IDs with the same COL A cell values and rank them. Then, using this ranking, you have to sum up the COL B value whose row ranking is 1.

https://app.smartsheet.com/b/publish?EQBCT=a645cb867c7142fa886ec4b2428515e6

MATCH approach

The RANKEQ function needs a unique number range to work in this situation. If you don't have unique values, you get ties. If you have some other columns whose cell values are unique, you can also use that.

If the unique cell values are not numbers but texts, you can use the MATCH function to create a row number column and count the number of the COL A cell value before the current row.

Row No=MATCH([Unique Text]@row, [Unique Text]:[Unique Text], 0)

Count =COUNTIFS([COL A]:[COL A], [COL A]@row, [Row No]:[Row No], <=[Row No]@row)

Matthew J McAteer · 12/21/23

Hi @Mariana B P

Is there a reason you have to have duplicate entries on this table? Perhaps the way to solve the problem is to exclude the duplicates prior to summing the values. Can you provide more insight into the specific use case?

Matthew

Mariana B P · 12/22/23

I gave a simple example above, but the sheet that i am working on includes several lines of tasks that are sometimes linked to the same budget bucket. For example: tasks 1, 3 and 5 are assigned to the same budget bucked, so the budget value will be the same for the 3 tasks. In the end of my table I want to sum up the total budget without the duplications.

jmyzk_cloudsmart_jp · 12/24/23

Hi @Mariana B P

First, you need to find which row is a duplicate.

RANKEQ approach

I would add an auto number 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)

The function is meant to collect Row IDs with the same COL A cell values and rank them. Then, using this ranking, you have to sum up the COL B value whose row ranking is 1.

https://app.smartsheet.com/b/publish?EQBCT=a645cb867c7142fa886ec4b2428515e6

MATCH approach

The RANKEQ function needs a unique number range to work in this situation. If you don't have unique values, you get ties. If you have some other columns whose cell values are unique, you can also use that.

If the unique cell values are not numbers but texts, you can use the MATCH function to create a row number column and count the number of the COL A cell value before the current row.

Row No=MATCH([Unique Text]@row, [Unique Text]:[Unique Text], 0)

Count =COUNTIFS([COL A]:[COL A], [COL A]@row, [Row No]:[Row No], <=[Row No]@row)

Mariana B P · 01/04/24

Thanks, it worked :)

Sum of values excluding rows with duplicate

Best Answer

Answers

Help Article Resources

Categories