Excel Formula - Aggregate rows by ignoring duplicated values in another column
debster
I have a table as below:
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:
Scott Craner
With FILTER in Office 365, put this in C2 and copy down:
=@FILTER($C$1:C1,($A$1:A1=A2)*($B$1:B1=B2),MAXIFS($C$1:C1,$A$1:A1,A2)+1)
If one does not have Office 365:
=IFERROR(INDEX($C$1:C1,AGGREGATE(15,7,ROW($C$1:C1)/(($A$1:A1=A2)*($B$1:B1=B2)),1)),IFERROR(AGGREGATE(14,7,$C$1:C1/($A$1:A1=A2),1),0)+1)
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Laravel 8, exception, unable to debug
- Next: How to parse and serialize an Abstract User in another model?
Related
TOP Ranking
- 1
Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)
- 2
Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background
- 3
pump.io port in URL
- 4
How to import an asset in swift using Bundle.main.path() in a react-native native module
- 5
Failed to listen on localhost:8000 (reason: Cannot assign requested address)
- 6
Spring Boot JPA PostgreSQL Web App - Internal Authentication Error
- 7
Emulator wrong screen resolution in Android Studio 1.3
- 8
3D Touch Peek Swipe Like Mail
- 9
Double spacing in rmarkdown pdf
- 10
Svchost high CPU from Microsoft.BingWeather app errors
- 11
How to how increase/decrease compared to adjacent cell
- 12
Using Response.Redirect with Friendly URLS in ASP.NET
- 13
java.lang.NullPointerException: Cannot read the array length because "<local3>" is null
- 14
BigQuery - concatenate ignoring NULL
- 15
How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?
- 16
ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3
- 17
Can a 32-bit antivirus program protect you from 64-bit threats
- 18
Make a B+ Tree concurrent thread safe
- 19
Bootstrap 5 Static Modal Still Closes when I Click Outside
- 20
Vector input in shiny R and then use it
- 21
Assembly definition can't resolve namespaces from external packages
Comments