Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
WSC WSC is offline
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,574
Default 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   Report Post  
Posted to microsoft.public.excel.misc
WSC WSC is offline
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,574
Default 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   Report Post  
Posted to microsoft.public.excel.misc
WSC WSC is offline
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,574
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy from a Cell to a text box. AJL Excel Worksheet Functions 9 November 7th 06 05:58 PM
count cells that begin with specific text mmer at steelcase Excel Worksheet Functions 7 July 25th 06 09:03 PM
vertical text across multiple cells BThrasher Excel Discussion (Misc queries) 3 December 5th 05 03:23 PM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 8 May 18th 05 04:23 AM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 0 May 15th 05 08:14 PM


All times are GMT +1. The time now is 08:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"