MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF ??


Posted by Jason on July 20, 2001 12:09 PM

Hi... I have a spreadsheet that lists all the PCs we have, and their IP addresses. I want to know how many PCs are within a certain IP range.

For example, I want to know how many computers use 10.3.200.*

Is CountIf the appropriate function? How do I do it? I can't enter 10.3.200.* as criteria obviously..


Posted by Mark W. on July 20, 2001 12:13 PM

=COUNTIF(A1:A100,"10.3.200*")

Posted by Jason on July 20, 2001 12:42 PM

Thanks for the tip, I realized my column was not in text format, that's why it wasn't working. :) But now I have a new problem. I need more than one criterion. ie. 10.3.200.* AND 10.4.100.* both need to be included in this count. countif will only allow me to enter 1 criteria.. Any ways around it?

Posted by Mark W. on July 20, 2001 12:47 PM

=COUNTIF(A1:A100,"10.3.200*")+COUNTIF(A1:A100,"10.4.200*")
is one, quick way.

Posted by Mark W. on July 20, 2001 12:47 PM

Make that...


Posted by Jason on July 20, 2001 12:55 PM

Re: Make that...

Duh thanks again :) I've got a lot of criteria (over 10) so I hope it all fits..


Posted by Mark W. on July 20, 2001 1:05 PM

In that case...

...consider using the following array formula:

{=SUM(ISNUMBER(MATCH(B1:B10&"*",A1:A100,0))+0)}

where your criteria are in B1:B10. Note: Array
formulas are entered using the Control+Shift+Enter
key combination. The braces, {}, are not entered
by you.


Posted by Jason on July 20, 2001 1:30 PM

Re: In that case...

Great.. I'll play with this formula. Thanks a bunch!