MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Blargh, another counting problem now..

Posted by Jason on July 20, 2001 3:34 PM

As per my previous post of counting PCs against their IP, that's done now.. NOW what I need is to count the number of PCs within the specified range that ALSO have less than a certain amount of hard disk space (another value on the worksheet)

So basically I need a COUNTIF that depends on two conditions. The help files make no mention of using two though, and array formulas seem to make no sense to me. Any clarification (mark? :)


Posted by Aladin Akyurek on July 20, 2001 3:58 PM


If you want to count things that must more than one criterion/condition (such counts I call multiconditional counts), COUNTIF will no longer suffice. You have 2 alternatives: an array formula and database count functions like DCOUNT.

With respect to the first, you can extend Mark's beautiful array formula to include the condition regarding hard disk space.

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

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

Archive2 contains some explanation on array formulas how they work.