Forum Discussion
Solved
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...
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.
Came across this formula recently, quickly became one of my favourites:
=SUMPRODUCT(--($A$2:$A$9=F2),1/COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,$B$2:$B$9))
(can be drag down)
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.
