Excel Formula – Aggregate rows by ignoring duplicated values in another column

I have a table as below:

enter image description here

I want to populate the Aggregate column by doing a cumulative sum based on ID and Code column. Some of the rows have duplicated values, like row 2 and row 4, row 3 and row 5 etc. So that I can show that ID 1 has 2 unique codes, ID 2 has 3 unique codes etc.

I have tried this formula but it doesn't yield what I want.

=COUNTIFS($B$2:B2,B2,$A$2:A2,A2)

My desired output is as below:

enter image description here



Read more here: https://stackoverflow.com/questions/66264037/excel-formula-aggregate-rows-by-ignoring-duplicated-values-in-another-column

Content Attribution

This content was originally published by debster at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: