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!
