| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count single Text in cells with multiple text entries
Current Array Formula:
=SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*((RawData!$DE$2:$DE$1757="A")*(RawData! $DE$2:$DE$1757="A B")*(RawData!$DE$2:$DE$1757="A B T")*(RawData!$DE$2:$DE$1757="A T"))) Where B3 is the organization, C3 is the personnel code, I want to count by organization then personnel code type, those that have "A" in column DE. DE lists the 4 text codes, and I need to count every instance that "A" appears. It is entered as an array. Currently returns 0, when there is text data to count. Thoughts? Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count single Text in cells with multiple text entries
Use the double negation operator to convert the array formula into something
that can be counted: http://www.dailydoseofexcel.com/arch...rray-formulas/ Dave -- Brevity is the soul of wit. "WSC" wrote: Current Array Formula: =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*((RawData!$DE$2:$DE$1757="A")*(RawData! $DE$2:$DE$1757="A B")*(RawData!$DE$2:$DE$1757="A B T")*(RawData!$DE$2:$DE$1757="A T"))) Where B3 is the organization, C3 is the personnel code, I want to count by organization then personnel code type, those that have "A" in column DE. DE lists the 4 text codes, and I need to count every instance that "A" appears. It is entered as an array. Currently returns 0, when there is text data to count. Thoughts? Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count single Text in cells with multiple text entries
Dave - thanks - just not sure where to use the double negation in this string
to get it to work. Have tried multiple locations with no success WSC "Dave F" wrote: Use the double negation operator to convert the array formula into something that can be counted: http://www.dailydoseofexcel.com/arch...rray-formulas/ Dave -- Brevity is the soul of wit. "WSC" wrote: Current Array Formula: =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*((RawData!$DE$2:$DE$1757="A")*(RawData! $DE$2:$DE$1757="A B")*(RawData!$DE$2:$DE$1757="A B T")*(RawData!$DE$2:$DE$1757="A T"))) Where B3 is the organization, C3 is the personnel code, I want to count by organization then personnel code type, those that have "A" in column DE. DE lists the 4 text codes, and I need to count every instance that "A" appears. It is entered as an array. Currently returns 0, when there is text data to count. Thoughts? Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count single Text in cells with multiple text entries
Try this:
=SUM(--(RawData!$CT$2:$CT$1757=$B3),--(RawData!$A$2:$A$1757=$C3),(--(RawData!$DE$2:$DE$1757="A"),--(RawData!$DE$2:$DE$1757="A B"),--(RawData!$DE$2:$DE$1757="A B T"),--(RawData!$DE$2:$DE$1757="A T"))) Does that work? -- Brevity is the soul of wit. "WSC" wrote: Dave - thanks - just not sure where to use the double negation in this string to get it to work. Have tried multiple locations with no success WSC "Dave F" wrote: Use the double negation operator to convert the array formula into something that can be counted: http://www.dailydoseofexcel.com/arch...rray-formulas/ Dave -- Brevity is the soul of wit. "WSC" wrote: Current Array Formula: =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*((RawData!$DE$2:$DE$1757="A")*(RawData! $DE$2:$DE$1757="A B")*(RawData!$DE$2:$DE$1757="A B T")*(RawData!$DE$2:$DE$1757="A T"))) Where B3 is the organization, C3 is the personnel code, I want to count by organization then personnel code type, those that have "A" in column DE. DE lists the 4 text codes, and I need to count every instance that "A" appears. It is entered as an array. Currently returns 0, when there is text data to count. Thoughts? Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count single Text in cells with multiple text entries
Just cracked it as I got your notification: This one did it. Changed from *
to + between my text items was all it took. =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*(--(RawData!$DE$2:$DE$1757="A")+(RawData!$DE$2:$DE$17 57="A B")+(RawData!$DE$2:$DE$1757="A B T")+(RawData!$DE$2:$DE$1757="A T"))) Thanks for the assistance! WSC "Dave F" wrote: Try this: =SUM(--(RawData!$CT$2:$CT$1757=$B3),--(RawData!$A$2:$A$1757=$C3),(--(RawData!$DE$2:$DE$1757="A"),--(RawData!$DE$2:$DE$1757="A B"),--(RawData!$DE$2:$DE$1757="A B T"),--(RawData!$DE$2:$DE$1757="A T"))) Does that work? -- Brevity is the soul of wit. "WSC" wrote: Dave - thanks - just not sure where to use the double negation in this string to get it to work. Have tried multiple locations with no success WSC "Dave F" wrote: Use the double negation operator to convert the array formula into something that can be counted: http://www.dailydoseofexcel.com/arch...rray-formulas/ Dave -- Brevity is the soul of wit. "WSC" wrote: Current Array Formula: =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*((RawData!$DE$2:$DE$1757="A")*(RawData! $DE$2:$DE$1757="A B")*(RawData!$DE$2:$DE$1757="A B T")*(RawData!$DE$2:$DE$1757="A T"))) Where B3 is the organization, C3 is the personnel code, I want to count by organization then personnel code type, those that have "A" in column DE. DE lists the 4 text codes, and I need to count every instance that "A" appears. It is entered as an array. Currently returns 0, when there is text data to count. Thoughts? Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count single Text in cells with multiple text entries
Right, a + works as well.
Dave -- Brevity is the soul of wit. "WSC" wrote: Just cracked it as I got your notification: This one did it. Changed from * to + between my text items was all it took. =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*(--(RawData!$DE$2:$DE$1757="A")+(RawData!$DE$2:$DE$17 57="A B")+(RawData!$DE$2:$DE$1757="A B T")+(RawData!$DE$2:$DE$1757="A T"))) Thanks for the assistance! WSC "Dave F" wrote: Try this: =SUM(--(RawData!$CT$2:$CT$1757=$B3),--(RawData!$A$2:$A$1757=$C3),(--(RawData!$DE$2:$DE$1757="A"),--(RawData!$DE$2:$DE$1757="A B"),--(RawData!$DE$2:$DE$1757="A B T"),--(RawData!$DE$2:$DE$1757="A T"))) Does that work? -- Brevity is the soul of wit. "WSC" wrote: Dave - thanks - just not sure where to use the double negation in this string to get it to work. Have tried multiple locations with no success WSC "Dave F" wrote: Use the double negation operator to convert the array formula into something that can be counted: http://www.dailydoseofexcel.com/arch...rray-formulas/ Dave -- Brevity is the soul of wit. "WSC" wrote: Current Array Formula: =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*((RawData!$DE$2:$DE$1757="A")*(RawData! $DE$2:$DE$1757="A B")*(RawData!$DE$2:$DE$1757="A B T")*(RawData!$DE$2:$DE$1757="A T"))) Where B3 is the organization, C3 is the personnel code, I want to count by organization then personnel code type, those that have "A" in column DE. DE lists the 4 text codes, and I need to count every instance that "A" appears. It is entered as an array. Currently returns 0, when there is text data to count. Thoughts? Thanks. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count single Text in cells with multiple text entries
Why not try the ever popular Sumproduct() function, where array entry is
*not* necessary: =SUMPRODUCT((RawData!$CT$2:$CT$1757=$B3)*(RawData! $A$2:$A$1757=$C3)*(RawData!$DE$2:$DE$1757={"A","AB ","ABT","AT"})) ? -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "WSC" wrote in message ... Just cracked it as I got your notification: This one did it. Changed from * to + between my text items was all it took. =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*(--(RawData!$DE$2:$DE$1757="A")+(RawData!$DE$2:$DE$17 57="A B")+(RawData!$DE$2:$DE$1757="A B T")+(RawData!$DE$2:$DE$1757="A T"))) Thanks for the assistance! WSC "Dave F" wrote: Try this: =SUM(--(RawData!$CT$2:$CT$1757=$B3),--(RawData!$A$2:$A$1757=$C3),(--(RawData!$DE$2:$DE$1757="A"),--(RawData!$DE$2:$DE$1757="A B"),--(RawData!$DE$2:$DE$1757="A B T"),--(RawData!$DE$2:$DE$1757="A T"))) Does that work? -- Brevity is the soul of wit. "WSC" wrote: Dave - thanks - just not sure where to use the double negation in this string to get it to work. Have tried multiple locations with no success WSC "Dave F" wrote: Use the double negation operator to convert the array formula into something that can be counted: http://www.dailydoseofexcel.com/arch...rray-formulas/ Dave -- Brevity is the soul of wit. "WSC" wrote: Current Array Formula: =SUM((RawData!$CT$2:$CT$1757=$B3)*(RawData!$A$2:$A $1757=$C3)*((RawData!$DE$2:$DE$1757="A")*(RawData! $DE$2:$DE$1757="A B")*(RawData!$DE$2:$DE$1757="A B T")*(RawData!$DE$2:$DE$1757="A T"))) Where B3 is the organization, C3 is the personnel code, I want to count by organization then personnel code type, those that have "A" in column DE. DE lists the 4 text codes, and I need to count every instance that "A" appears. It is entered as an array. Currently returns 0, when there is text data to count. Thoughts? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy from a Cell to a text box. | Excel Worksheet Functions | |||
| count cells that begin with specific text | Excel Worksheet Functions | |||
| vertical text across multiple cells | Excel Discussion (Misc queries) | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Hybrid Mode
