| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting rows from columns on multiple worksheets? | Excel Worksheet Functions | |||
| Counting from multiple columns | Excel Discussion (Misc queries) | |||
| Counting rows based on multiple columns | Excel Worksheet Functions | |||
| Counting values in multiple columns | Excel Discussion (Misc queries) | |||
| Help w/ counting multiple columns based on IF criteria | Excel Worksheet Functions | |||
Linear Mode
