asked on # 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

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

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

By the way I am not using RIGHT but I am using COLUMN

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

.... Thinkpads_User

.... Thinkpads_User

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

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.

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.

Brad,

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!

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!

Thank you very much - I really appreciate it!

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)))

Brad

If you

=SUM(ISNUMBER(SEARCH("x",A

Brad

Brad,

Thanks for the extra tip!

Frank

Thanks for the extra tip!

Frank

... Thinkpads_User