=SUMPRODUCT(--(A2:A100="ALL"),--(A2:A100<"Closed"))

note that unless you have Excel 2007 you cannot use A:A and instead you need
to specify a range


--


Regards,


Peo Sjoblom


"WastingTime" wrote in message
...
I'm trying to perform a boolean test across multiple columns of arrays.

The test in on worksheet 1, the data on worksheet 2.
I have columns such:
Column A Column B
5 <blank
7 <blank
ALL <blank
7 Closed
ALL Closed
5 <blank
All <blank
7 <blank
5 Closed

The objective is to count the instances of "ALL" in column, but only if
the
matching cell in Column B is not equal to "CLOSED". Or is <blank if you
prefer.

=SUM(IF(TBD!$A:$A="ALL",1,0)) does not work when I do the CTRL-SHFT-ENTER.
I get the #num error.

So how do I get a counting function for text that tests all occurrences in
column A unless it fails column B? or alternatively Column A is true and
the
matching column B cell is blank?

--
WT