Function Problems

How to Count Duplicate Email Addresses and List Source Sheets in Excel

Elise Williams Oct 21, 2026 868 views

Question details

The user needs to display unique email addresses from combined lists, count how many times each address appears, and output a comma-separated list of the source spreadsheet numbers.

How to Count Duplicate Email Addresses and List Source Sheets in Excel

Product: Excel
Device & OS: not provided
Scenario: Consolidating multiple mailing lists or datasets into a single master sheet and analyzing the frequency and origin of duplicate entries.
Observed behavior: The user wants a streamlined report displaying each email address exactly once, alongside its occurrence count and the corresponding source sheet numbers (e.g., '10, 11').

Before you start

Ensure your combined data is organized into columns on a single worksheet, with the source spreadsheet numbers in Column A and the email addresses in Column B.

Solution 1Recommended

Use COUNTIF, FILTER, and TEXTJOIN Functions

Combine modern dynamic array functions to extract unique values, count occurrences, and concatenate matching data source numbers in a single step.

This method utilizes the FILTER function to find all rows matching a specific email address, and TEXTJOIN to combine the corresponding source sheet numbers into a neat, comma-separated list.

Extract unique email addresses

Select an empty cell in a new column (e.g., E2) and type the formula =UNIQUE(B2:B1000). Press Enter to generate a clean list of email addresses with duplicates removed.

Count email occurrences

In the cell adjacent to your first unique email (e.g., F2), type =COUNTIF($B:$B, E2). Press Enter, then click and drag the fill handle down to apply this count to all listed emails.

List the source spreadsheet numbers

In the next column (G2), input the formula =TEXTJOIN(", ", TRUE, FILTER($A$2:$A$1000, $B$2:$B$1000=E2, "")). Press Enter and drag the fill handle down. This will match the email address in E2 to Column B, and return the corresponding sheet numbers from Column A, separated by commas.

Use COUNTIF, FILTER, and TEXTJOIN Functions

Compatibility Note: The FILTER and UNIQUE functions require Microsoft 365 or modern spreadsheet software versions that support dynamic arrays.

Solution 2

Use an Array Formula with IF and TEXTJOIN

An alternative approach for older versions of Excel that do not support the FILTER function.

Easily Manage Duplicates and Advanced Formulas with WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and TEXTJOIN out of the box. It simplifies analyzing combined datasets, counting duplicates, and extracting complex data without requiring expensive subscriptions or convoluted workarounds.

1. Open your combined data file: Launch WPS Spreadsheet and open the workbook containing your consolidated email lists and source numbers.
2. Apply dynamic arrays: Use the =UNIQUE() function in an empty column to instantly generate your list of single, non-duplicated email addresses.
3. Extract counts and sources: Apply the =COUNTIF() and =TEXTJOIN() combined with =FILTER() exactly as you would in Microsoft 365 to instantly analyze your data sources.

Seamlessly compatible with Microsoft Excel formulas and .xlsx files.Built-in support for dynamic arrays like FILTER and UNIQUE to automate data extraction.Lightweight architecture ensures smooth performance even when processing massive mailing lists.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my TEXTJOIN and FILTER formula return a #VALUE! error?

This error usually occurs when the array ranges in your formula do not match in size. Ensure that the source number range (e.g., $A$2:$A$1000) and the criteria range (e.g., $B$2:$B$1000) have the exact same number of rows.

How can I extract unique email addresses before counting them if I don't have the UNIQUE function?

You can manually extract them by copying the column of email addresses, pasting it into a new column, and then selecting Data > Remove Duplicates from the top ribbon menu.

Can I count duplicates across different sheets without combining them first?

While it is technically possible using complex 3D references or Power Query, it is highly recommended to combine your data into a single master sheet first. This makes standard functions like COUNTIF and TEXTJOIN much more effective and less prone to calculation errors.

Excel & Data Problems