# Problem with counting

Folks,
In Column A of the attached workbook I have various IP addresses, some with one to three "xxx". In D3:D5 I have an array formula using SUM, MID, COLUMN functions.

I'm not getting the expected results. I am wanting to know how many IP address's have one "x", two, "xx" and three "xxx". I get the three "xxx" but that all.
COUNT.xlsm
Microsoft Excel

Last Comment
Frank Freese

8/22/2022 - Mon
John

The cells are formatted as General. Try formatting the cells you are test as Text.  I think they need to be formatted as Text for MID, LEFT and RIGHT to work.

Frank Freese

I changed the formatting to text but the results were the same.
By the way I am not using RIGHT but I am using COLUMN
John

I don't know. You are using Array formulas and I have not used these.

Frank Freese

Thanks for the time. Let's see what other's will say.
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Frank Freese

Thanks for the explanation. Why didn't I use COUNTIF? Let's just say I had another senior moment and then I wanted to experiment more with using arrays.
Great solution - great job!
Frank Freese

Thank you very much - I really appreciate it!
byundt

fh_freese,
If you really want to use an array formula, consider one like this to find the number of cells containing "x" (but not "xx" or "xxx"):
=SUM(ISNUMBER(SEARCH("x",A2:A11)) - ISNUMBER(SEARCH("xx",A2:A11)))