Calculating Domains and Counts

Peter has a worksheet that contains a client list. In column D is the email address for each client. He would like to get a list of unique domains from those addresses (the part after the @ sign), along with a count of how many email addresses use each domain. There are several thousand clients in the worksheet.

The answer to this problem depends, largely, on the version of Excel you are using. If you are using Excel 2024 or later (including Microsoft 365), you could put the following formula into cell F2:

=UNIQUE(TEXTAFTER(D2:D3751, "@"))

The formula assumes that the first row of your data is used for column headings and that your data doesn't extend beyond row 3751. You can then place the following formula in cell G2:

=COUNTIF(D$2:D$3751, "*"&F2)

Copy it down as many cells as necessary and you then have your counts for each domain.

If you are using Microsoft 365, then you could replace the above formulas with a single formula in cell F2:

=GROUPBY(TEXTAFTER(TRIMRANGE(D2:D9999), "@"), TRIMRANGE(D2:D9999), COUNTA, 0, 0, -2)

A shorter variation on this formula is the following:

=LET(d,TEXTAFTER(TRIMRANGE(D2:D9999),"@"),GROUPBY(d,d,COUNTA,,0))

Another approach if you have Excel 2024 or later is to use the following single formula in F2:

=LET(r, D2:D9999, u, TOCOL(UNIQUE(TEXTAFTER(r, "@")), 3), HSTACK(u, COUNTIF(r, "*" & u)))

The purpose of the TOCOL function is to remove blanks and errors from the array gyrations used by the formula. You can get rid of the TOCOL function if you, instead, use the TRIMRANGE function. This formula requires Microsoft 365:

=LET(r, TRIMRANGE(D2:D9999), u, UNIQUE(TEXTAFTER(r, "@")), HSTACK(u, COUNTIF(r, "*" & u)))

There is one nuance of email addresses that someone is bound to bring up—it is technically permissible to have more than one @ sign in an address. The following, for example, is a valid email address:

"john@office"@mydomain.com

This is valid because the local part of the address can contain a quoted string, and just about anything can be included in the string, include an @ sign. This is described in section 3.2.4 of RFC 5322 which, among other things, defines formats used when addressing email messages. If you enjoy geek speak, you can find more information about this RFC here:

https://www.rfc-editor.org/rfc/rfc5322

The quoting can only be done in the local portion of email addresses, so the logical solution to deal with such situations is to modify the TEXTAFTER function so it looks for the first @ sign starting from the end of the address. Here, for example, is the last formula with the adjustment to TEXTAFTER:

=LET(r, TRIMRANGE(D2:D9999), u, UNIQUE(TEXTAFTER(r, "@",-1)), HSTACK(u, COUNTIF(r, "*" & u)))

Everything so far has focused on using Excel 2024 or later versions. If you are using an older version of the program, then your best bet is going to be to use a macro. The following steps through the addresses in column D and creates two collections, one for domain names and one for counts of each domain name. The output is then added to columns H and I.

Sub ListDomains()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim emailAddr As String
    Dim domain As String
    Dim atPos As Long
    Dim Domains As New Collection
    Dim Counts As New Collection
    Dim i As Long
    Dim Found As Boolean
    Dim outRow As Long
    Dim newCount As Long

    Set ws = ActiveSheet

    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row

    For r = 2 To lastRow
        emailAddr = Trim(ws.Cells(r, "D").Value)

        If Len(emailAddr) > 0 Then
            atPos = InStrRev(emailAddr, "@")
            If atPos > 0 And atPos < Len(emailAddr) Then
                domain = LCase(Mid(emailAddr, atPos + 1))

                Found = False
                For i = 1 To Domains.Count
                    If Domains(i) = domain Then
                        newCount = Counts(i) + 1
                        Counts.Remove i
                        If i > Counts.Count Then
                            Counts.Add newCount
                        Else
                            Counts.Add newCount, , i
                        End If
                        Found = True
                        Exit For
                    End If
                Next i

                If Not Found Then
                    Domains.Add domain
                    Counts.Add 1
                End If
            End If
        End If
    Next r

    ' The following lines erase whatever is in columns H and I.
    ' Modify column references if output should go to a different
    ' set of columns.
    ws.Range("H:I").ClearContents
    ws.Range("H1").Value = "Domain"
    ws.Range("I1").Value = "Count"

    outRow = 2
    For i = 1 To Domains.Count
        ws.Cells(outRow, "H").Value = Domains(i)
        ws.Cells(outRow, "I").Value = Counts(i)
        outRow = outRow + 1
    Next i
End Sub

When you run the macro, anything in columns H and I is deleted. If you want the output in a different set of columns, you should modify the column references indicted near the end of the macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (953) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.