counting text across multiple columns

[Guest]

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?

 

[Peo Sjoblom]

=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

 

[David Biddulph]

=SUMPRODUCT(--(A2:A100="ALL"),--(B2:B100<>"Closed")) presumably? [Column B
for the second term?]

 

[Peo Sjoblom]

Yes, thank you


--


Regards,


Peo Sjoblom

=SUMPRODUCT(--(A2:A100="ALL"),--(B2:B100<>"Closed")) presumably? [Column
B for the second term?]
--
David Biddulph

=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