Count instances of an e-mail domain name across multiple e-mail addresses

[Alice1]

Hi, so Row A contains lots of different but unique individual e-mail addresses, thousands in fact in the format :

Alan.Smith@MrExcel.com
Karen.Jones@MrExcel.com
Clive.Bloggs@Microsoft.com
Brenda.Forbes@MrExcel.com

what I need to do is count the instances that an e-mail Domain Name appears so in the above example :

MrExcel.com = 3
Microsoft.com = 1

I don't have a clue how to do this though, without first separating the cell into two different cells which I don't really want to do.

Hopefully someone can help me please ?

 

[mrshl9898]

Hi, how about?

In B2: =MID(A2,FIND("@",A2),LEN(A2))
In C2: =COUNTIF(B:B,B2)

*Not sure if this is against the splitting of the cell criteria

 

[welshgasman]

I'd be interested in how it could be done without VBA and splitting the cells as well.

 

[Fluff]

Still needs a helper column

Excel 2013/2016
	A	B	C
2	Alan.Smith@MrExcel.com	MrExcel.com	4
3	Karen.Jones@MrExcel.com	Microsoft.com	1
4	Clive.Bloggs@Microsoft.com	adomain.co.uk	1
5	Brenda.Forbes@MrExcel.com
6	JoeBloggs@adomain.co.uk
7	Fluff@mrexcel.com
Sheet1

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(ISNUMBER(SEARCH($B2,$A$2:$A$7))))
B2	{=INDEX((RIGHT($A$2:$A$7,LEN($A$2:$A$7)-FIND("@",$A$2:$A$7))),MATCH(0,COUNTIF($B$1:$B1,(RIGHT($A$2:$A$7,LEN($A$2:$A$7)-FIND("@",$A$2:$A$7)))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.