How to Count Unique Domains for Each Account ID in Excel
Question details
The user needs to calculate the number of distinct domain names associated with specific account IDs in a spreadsheet dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing account data where each account might have multiple domain records, including duplicate domains, and determining the exact count of unique domains per account.
- Observed behavior
- The user requires a formula to either count unique domains for one specific account or generate a summary table of unique domain counts for all accounts.
Ensure your data is organized in clean columns (e.g., Account IDs in Column A and Domains in Column B) without any blank rows interrupting the dataset.
Use UNIQUE and FILTER Functions (Microsoft 365 & Excel 2021)
The most efficient method for modern Excel versions to count unique values based on specific criteria.
This method utilizes dynamic array functions available in newer versions of Excel. It filters the domains to match your specific account ID, extracts the unique domains from that filtered list, and then counts the total rows returned.
Click the cell where you want the unique count for a specific Account ID to appear.
Type the formula =ROWS(UNIQUE(FILTER($B$2:$B$9, $A$2:$A$9=F2))). This assumes Column A contains Account IDs, Column B contains Domain Names, and F2 contains the specific Account ID you are evaluating.
Press Enter. The formula will instantly output the count of distinct domains for that specific account.
Use SUMPRODUCT and COUNTIFS Functions (All Excel Versions)
A reliable approach for older versions of Excel that do not support dynamic array functions.
Count Unique Values Easily in WPS Spreadsheet
WPS Spreadsheet supports advanced array formulas like UNIQUE and FILTER, allowing you to seamlessly process complex data just like in modern Excel. Experience lightweight, fast, and highly compatible data analysis for free.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the Account IDs and Domains.
- 2. Apply the UNIQUE formula: Use the =ROWS(UNIQUE(FILTER(B2:B9, A2:A9=F2))) formula in your target cell.
- 3. Analyze instantly: Press Enter to get your distinct domain counts without needing expensive software subscriptions.
Frequently Asked Questions
Can I use a Pivot Table to count unique domains per account ID?
Yes. When creating a Pivot Table, check the box 'Add this data to the Data Model'. Place Account ID in the Rows area and Domain in the Values area. Then, right-click the Domain values, choose 'Value Field Settings', scroll to the bottom, and select 'Distinct Count'.
Why does my SUMPRODUCT formula return a #DIV/0! error?
This error typically occurs if there are blank cells in your selected data range. Ensure the ranges in your COUNTIFS function do not include empty rows, or adjust the formula to safely handle blanks.
Are the UNIQUE and FILTER functions available in older Excel versions?
No, UNIQUE and FILTER are dynamic array functions available only in Microsoft 365 and Excel 2021 or later. For Excel 2019 and older, you must use the SUMPRODUCT and COUNTIFS method.
Does WPS Office support dynamic array formulas like UNIQUE?
Yes, the latest versions of WPS Spreadsheet fully support modern dynamic array functions including UNIQUE, FILTER, and SORT, ensuring seamless formula compatibility with Excel 365.
