If you need to count how many email addresses are from the same domain using Excel, there is a nice trick which means you don’t need to go through the whole process of first splitting the name and domain name apart.
Table of contents
YouTube Count emails based on domain name
Count all email addresses that CONTAIN domain text
As shown below, we have the list of email addresses (1), and we want to count how many are from each of the domain names (2).
In this case we have used a COUNTIFS where the criteria range is column A. The trick is in the criteria. Note that when we refer to cell C4 above (3 or 4), we have wrapped asterisk around the link.
So instead of referring to
- cell C4,
- we refer to “*”&C4&”*”
By putting a * in front and behind the reference, we are telling Excel to count any thing that contains what is in C4. * in Excel is a wildcard character which represents any character AND any number of characters (as a apposed to a ? which is any character but each ? represents a single character).
Count all email addresses that END with certain text
Using the same logic we can also ask Excel to count all the email addresses that END with certain text. This eliminates the risk that the domain name is repeated in the name part of the email address.
As shown below, the only difference is the criteria used (1). In this case we only have the asterisk in front of the link. We are telling Excel anything (any character and any number of characters) can be in front of the link, but the cell MUST end with the link we have specified to be counted.
You could similarly count cells that START with certain text by only having an asterisk at the end.
Related
Find email addresses with same name different domains in Excel