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.

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.

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.

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.

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.

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.

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.