|
counting text across multiple columns
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 |
counting text across multiple columns
=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 |
counting text across multiple columns
=SUMPRODUCT(--(A2:A100="ALL"),--(B2:B100<"Closed")) presumably? [Column B
for the second term?] -- David Biddulph "Peo Sjoblom" wrote in message ... =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 "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 |
counting text across multiple columns
Yes, thank you
-- Regards, Peo Sjoblom "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =SUMPRODUCT(--(A2:A100="ALL"),--(B2:B100<"Closed")) presumably? [Column B for the second term?] -- David Biddulph "Peo Sjoblom" wrote in message ... =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 "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 |
| All times are GMT +1. The time now is 07:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com