counting text across multiple columns

W

WastingTime

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?
 
P

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
 
D

David Biddulph

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

Peo Sjoblom

Yes, thank you


--


Regards,


Peo Sjoblom


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

Peo Sjoblom said:
=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top