Hey Aladin.. still fiddling with multi conditional counts

Posted by Jason on July 23, 2001 8:57 AM

=SUM((ISNUMBER(MATCH(B1:B10&"*",A1:A100,0))+0)*(C1:C100 &LT;= an-amount))

entered as an array formula, where C1:C10 contains numbers indicating available hard disk space.

^^ I've been playing with the above formula. If you recall I was trying to count the number of PCs on a list that fall under a specific IP range and that have less than a certain amount of disk space. I ended up with this formula:

=ISNUMBER(MATCH("10.10.*",'Desktops needing Upgrade'!B5:B100,0))+0

entered as an array formula. This result only gives 1, which I expect. however:

=SUM(ISNUMBER(MATCH("10.10.*",'Desktops needing Upgrade'!B5:B100,0))+0)

This formula should give back the number of PCs within that IP range, shouldn't it? Instead, I get the same result, 1! How can this be? I have a hell of a lot of computers within the 10.10.* range.

Thanks

Posted by Aladin Akyurek on July 23, 2001 10:04 AM

Jason,

Consider the following fake data that occupy the range B1:C6.

{"10.10.n1",200;"10.10.n2",100;"10.10.n3",20;"10.11.n4",40;"10.12.n5",100;"10.10.n6",140}

In C, I've put some numbers that stand for the unused disk space:

To count PC that fall under a specific range, say the IPs that start with 10.10., use:

=SUMPRODUCT((ISNUMBER(SEARCH("10.10.*",B1:B6)))+0)

This is equivalent in effect to the following array formula:

{=SUM((ISNUMBER(SEARCH("10.10.*",B1:B6)))+0)}

Note that I replaced MATCH by SEARCH (I'll look into MATCH that seems misbehaves in this context!)

To count the number of PCs on a list that fall under a specific IP range and that have less than a certain amount of disk space, use:

=SUMPRODUCT(((ISNUMBER(SEARCH("10.10.*",B1:B6)))+0)*(C1:C6&LT;=100))

This is equivalent in effect to the following array formula:

{=SUM(((ISNUMBER(SEARCH("10.10.*",B1:B6)))+0)*(C1:C6&LT;=100))}

Note. I suspect that the solutions with SUMPRODUCT formulas might be more efficient than the corresponding array-formulas, but I've no evidence yet on this.

Aladin

============

Posted by Jason on July 23, 2001 10:23 AM

It's working!!

Aladin, your last suggestion seems to have hit the nail on the head! I will continue to play with this formula, and I'll let you know how it goes. I'm not sure why the array formula didn't work, it must be something I was doing incorrectly..

Posted by Jason on July 23, 2001 10:31 AM

Quick question..

If I want to use another condition, do I modify the formula to show this:

=SUMPRODUCT(((ISNUMBER(SEARCH("10.10.*",B1:B6)))+0)*(C1:C6&LT;=100)*(D1:D6&LT;=128000))

I will want it to count the PC if it falls within the IP range, has less than 100 mb disk space, AND has less than 128 mb memory. Logically, this should work..

Posted by Aladin Akyurek on July 23, 2001 10:42 AM

Re: Quick question..

Jason -- That's right.
The array-formulas with SEARCH work also. The problem was with MATCH, a matter that I'll look into (a research issue, so to speak). Note that you've been the first person to whom I gave a SUMPRODUCT solution for a multiconditional count problem, along with the array formulas. That's something.

Aladin

Posted by Jason on July 23, 2001 11:03 AM

Re: Quick question..

I feel honoured! :) Thanks for your help, my boss won't be ripping me a new one today!!