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
Last Comment
Frank Freese
ASKER
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
ASKER
Thanks for the time. Let's see what other's will say.
ASKER CERTIFIED SOLUTION
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
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.
ASKER
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!
ASKER
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",A
Brad
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",A
Brad
ASKER
Brad,
Thanks for the extra tip!
Frank
Thanks for the extra tip!
Frank
... Thinkpads_User