COUNTIF

[Tim Kuhn]

Hello

I have a question regarding counting values and cross referencing to a range
on another worksheet.

The values are IP Addresses (10.64.xxx), so I guess my first question is
whether it is possible to assign them to particular ranges.

I have two worksheets, one being the source file, with column E containing
IP addresses. The other worksheet contains IP address ranges in columns
A&B.

I am basically looking to do a count of the source file addresses and
allocate by range.

I'm currently using the COUNTIF formula below but have not had any luck:

=COUNTIF(source!$E$2:$E$9686, AND(">="&A3,"<="&B3))

Any suggestions are appreciated

Regards
Tim Kuhn

 

[Stephen]

Hello

I have a question regarding counting values and cross referencing to a
range on another worksheet.

The values are IP Addresses (10.64.xxx), so I guess my first question is
whether it is possible to assign them to particular ranges.

I have two worksheets, one being the source file, with column E containing
IP addresses. The other worksheet contains IP address ranges in columns
A&B.

I am basically looking to do a count of the source file addresses and
allocate by range.

I'm currently using the COUNTIF formula below but have not had any luck:

=COUNTIF(source!$E$2:$E$9686, AND(">="&A3,"<="&B3))

Any suggestions are appreciated

Regards
Tim Kuhn

You face two problems:
1 There cannot be more than one condition in COUNTIF.
2 The format of IP addresses (containing multiple dots) means that in Excel
they are text strings, not numbers.

The first is easily overcome by using SUMPRODUCT rather than COUNTIF:
=SUMPRODUCT(--(source!$E$2:$E$9686>=A3),--(source!$E$2:$E$9686<=B3))

The second is more tricky! For example, comparison of text is done on a
character by character basis, so 2 will be seen as greater than 10. And the
fields within an IP address are not of a standard length: both 1.2.3.4 and
111.222.333.444 are valid IP addresses! If you are just working with the
last field (xxx in your example), you may be able to separate this out using
text string functions (such as LEN, FIND, SEARCH , LEFT, RIGHT, MID), and
then convert to a number with VALUE.

 

[daddylonglegs]

What values do you have in A3 and B3?

If you want to count IP addresses that start with a specific string you
could use a wildcard in COUNTIF like

=COUNTIF(source!$E$2:$E$9686,"10.64.*")