• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count of duplicate IP addresses in a range

J

JCTalk

Member
Hiya,

I have a long range list (column A) with IP addresses, blanks and "No IP address" phrase.

e.g.
xx.xx.xx.xxx
xx.xx.xx.xxx
No IP address

xx.xx.xx.xxx
No IP address
No IP address

I would like a formula to put in a single cell that counts the total number of duplicate IP addresses (ignoring the blanks & the term "No IP address") in that range. I can't use a helper column to check how many times each IP appears.

Is this possible?

Thanks in advance guys.
 
Hi Faseeh,

Thank you very much for replying.

I've tried your formula (with ctrl+shift+enter) and it seems to produce "1" all the time. I've done a manual check on the IP's and it tells me there are 44 duplicated IP's.

What am I missing?
 
I don't have a sheet I can upload, but I've tried it on the following, and I assume the principal is the same...

A100
A100
A100
A101
A101
A101
A102
A105
A105
A109
A109
No IP Address
No IP Address

So in that list I count 10 duplicates, but the formula produces 2.

Many thanks Faseeh
 
JCTalk said:
I've tried it on the following, and I assume the principal is the same...
A100
A100
A100
A101
A101
A101
A102
A105
A105
A109
A109
No IP Address
No IP Address

So in that list I count 10 duplicates....
Click to expand...

Try,

=SUMPRODUCT((A1:A100<>"")*(A1:A100<>"No IP Address")*(COUNTIF(A1:A100,A1:A100)>1))

Remark :

1] Change range to suit.

2] The result is 10 according to Post #.5 example data in A1:A13.

Regards
Bosco
 
Last edited:
Hello JCTalk,

I regret the formula i mentioned count "Unique" values and not duplicates. Please go by @Bosco's formula as it is producing correct results.

Thank you.
 
You must log in or register to reply here.
Back
Top