Sum of values excluding rows with duplicate
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.
Best Answer
-
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.
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)
Answers
-
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
-
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.
-
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.
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)
-
Thanks, it worked :)
Help Article Resources
Categories
Check out the Formula Handbook template!
