Forum Discussion
Formula Help: Count Unique Domains per Account ID
Hello Community!
I have an Excel file with two columns: Account ID and Domain Name. A single Account ID can have multiple Domain Names associated with it. In other words, the relationship between Account ID and Domain Name is one-to-many (1:N). What's the best way to pull the number of UNIQUE domains associated with a specific account ID? Here is what the sample data might look like:
| Account ID | Domain Name |
| 1234 | f.com |
| 1234 | e.com |
| 1234 | g.com |
| 4321 | a.com |
| 4321 | b.com |
| 4321 | b.com |
| 5678 | z.com |
| 5678 | y.com |
For Account ID 1234, there are 3 unique domains. Similarly, there are 2 unique domains for account IDs 4321 and 5678. Can anyone please assist.
The formula in the sample file returns the intended result. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
10 Replies
If for legacy Excel we may add one more
For Account ID
=IFERROR( INDEX( accountID, AGGREGATE(15,6, (ROW(accountID)-ROW($A$1)) / (COUNTIF($E$14:E14, accountID)=0), 1) ), "" )for # of Domains
=IF( $E15="", "", SUMPRODUCT(--(accountID=$E15)/COUNTIFS(DomainName,DomainName) ) )and drag both down
- Brilliant! That is exactly why excel is fun!
- Thanks, Rachel!
- Glad you like it.
If on Excel 365
=LET( u, UNIQUE(accountID), count, MAP(u, LAMBDA(a, ROWS(UNIQUE(FILTER(DomainName, accountID=a))) ) ), VSTACK( {"Account ID","# of Domains"}, HSTACK(u, count)) )- Thanks, Sergei!
yashank_p , you are welcome
The formula in the sample file returns the intended result. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
Thanks so much for all the help, OliverScheurich! Keep inspiring
