Can I Use a Count Function for Text?

[Richard Horn]

For instance:

Project Lead Status Date
Chris Craig Client Testing 05/15/09
Chris Craig Completed 04/17/09

What I want to return is "1". In other words, all projects that "chris
craig" is working on, that the project status is not "completed" or the
status is not "carried forward".

hope this helps.

 

[David Biddulph]

So you've confirmed that you have a problem, and that your formula doesn't
match your data, but you've not bothered with my other suggestions?

 

[Richard Horn]

Yes David, that's affirmative. I am certainily stuck.

I tried your 3 suggestions:
1. =('Q2'!E8<>"completed, carried forward") which returned all TRUE even
though column does contain a Completed status.
2. =('Q2'!E8="completed, carried forward") which returned all FALSE even
though column does contain a Completed status.
3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does contain
a Completed status.

Each of your suggested helpers I pasted in a cell, then copied them down to
cover a range of cells I wanted to test query.

Not unless I am totally misunderstanding what your trying to help me with, I
am not sure how to make this work, but I certainly appreciate your patience
is trying to share your experience.

thanks Richard

 

[David Biddulph]

As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested earlier)
that the content of your cell is not "completed, carried forward", because
if you copy the string "completed, carried forward" [without the quotes]
into the cell and use the LEN function on that you will see that it IS 26
characters.
What does =LEN('Q2'!E8) return? How many characters are in the cell, if it
isn't 26?
What does =CODE(RIGHT(A8)) return? It will be 100 if the last character in
the cell is "d". It will be 32 if the last character is a space, and 160
if it is a non-breaking space.

 

[Richard Horn]

Oh my gosh! David I am so sorry.

The cell I am quering does not contain "completed, carried forward"

The cell would contain either "completed" or "carried forward". It would be
one or the otther.

I wanted to capture who the project lead is in column D, "chris craig" and
any project she is working on, unless the status is "completed" or "carried
forward" in column E. If the column E entry is "completed" or "carried
forward" I do not want that in the count.

Again, I apologize for not being so precise, as I know when making a formula
in Excel, you must be spot on.

richard

As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested earlier)
that the content of your cell is not "completed, carried forward", because
if you copy the string "completed, carried forward" [without the quotes]
into the cell and use the LEN function on that you will see that it IS 26
characters.
What does =LEN('Q2'!E8) return? How many characters are in the cell, if it
isn't 26?
What does =CODE(RIGHT(A8)) return? It will be 100 if the last character in
the cell is "d". It will be 32 if the last character is a space, and 160
if it is a non-breaking space.
--
David Biddulph

Yes David, that's affirmative. I am certainily stuck.

I tried your 3 suggestions:
1. =('Q2'!E8<>"completed, carried forward") which returned all TRUE
even though column does contain a Completed status.
2. =('Q2'!E8="completed, carried forward") which returned all FALSE
even though column does contain a Completed status.
3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does
contain a Completed status.

Each of your suggested helpers I pasted in a cell, then copied them
down to cover a range of cells I wanted to test query.

Not unless I am totally misunderstanding what your trying to help me
with, I am not sure how to make this work, but I certainly appreciate
your patience is trying to share your experience.

thanks Richard

 

[David Biddulph]

OK, now that you realise that to get the right answer you must ask the right
question, let's go back a few days.

Try changing
=SUMPRODUCT(('Q2'!D833="chris craig")*('Q2'!E8:E33<>"completed, carried
forward"))
to
=SUMPRODUCT(('Q2'!D833="chris
craig")*('Q2'!E8:E33<>"completed")*('Q2'!E8:E33<>"carried forward"))
--
David Biddulph

Oh my gosh! David I am so sorry.

The cell I am quering does not contain "completed, carried forward"

The cell would contain either "completed" or "carried forward". It
would be one or the otther.

I wanted to capture who the project lead is in column D, "chris
craig" and any project she is working on, unless the status is
"completed" or "carried forward" in column E. If the column E entry
is "completed" or "carried forward" I do not want that in the count.

Again, I apologize for not being so precise, as I know when making a
formula in Excel, you must be spot on.

richard

As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested
earlier) that the content of your cell is not "completed, carried
forward", because if you copy the string "completed, carried
forward" [without the quotes] into the cell and use the LEN function
on that you will see that it IS 26 characters.
What does =LEN('Q2'!E8) return? How many characters are in the
cell, if it isn't 26?
What does =CODE(RIGHT(A8)) return? It will be 100 if the last
character in the cell is "d". It will be 32 if the last character
is a space, and 160 if it is a non-breaking space.
--
David Biddulph

Yes David, that's affirmative. I am certainily stuck.

I tried your 3 suggestions:
1. =('Q2'!E8<>"completed, carried forward") which returned all TRUE
even though column does contain a Completed status.
2. =('Q2'!E8="completed, carried forward") which returned all FALSE
even though column does contain a Completed status.
3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does
contain a Completed status.

Each of your suggested helpers I pasted in a cell, then copied them
down to cover a range of cells I wanted to test query.

Not unless I am totally misunderstanding what your trying to help me
with, I am not sure how to make this work, but I certainly
appreciate your patience is trying to share your experience.

thanks Richard

:

So you've confirmed that you have a problem, and that your formula
doesn't match your data, but you've not bothered with my other
suggestions? --
David Biddulph

Richard Horn wrote:
With the helper column I copied down, it did not return as it
should:

Whether I used =('Q2'!E8<>"completed, carried forward") which
returned all TRUE even though column does contain a Completed
status.

or

=('Q2'!E8="completed, carried forward") which returned all FALSE
even though column does contain a Completed status.

For column E on my Q2 worksheet I am using a Validation, List,
from the bottom of my sheet, so the project lead can have a
drop-down box to select their current status. So there would not
be any abnormal characters or spaces when I am trying to comprise
my formula.

:

That probably means that you've got some slight difference in the
content of column E, perhaps spare spaces or other characters
(such as non-breaking spaces) in the cell.

You can check with a helper column:
=('Q2'!E8<>"completed, carried forward") and copy down
and/or
=('Q2'!E8="completed, carried forward") and copy down.

You might also want to try =LEN('Q2'!E8)=26

You might wish to copy the string (which you think is
="completed, carried forward") from a suitable cell in column E
into your formula. --
David Biddulph

message
I found these comments very helpful so far. Now I am stumped.

I have a workbook with multiple worksheets and I am making a
summary page up
front for management review.

Here's what I am trying to do. From worksheet named Q2, I have
all my projects leads in column D, and then the current status
of their projects in
column E, for instance, intake, editing, pending, etc.. What I
am trying to
do is count all the instances of one project lead, in this case
chris craig,
and then I want to know all projects she is working on, unless,
or except, if
the status is completed or carried forward. If the status for a
project lead
is completed or carried, then do not count.

This is the formula I am trying to use, but is is counting all
projects for
the given lead with all statuses, including completed and
carried forward.

=SUMPRODUCT(('Q2'!D833="chris
craig")*('Q2'!E8:E33<>"completed, carried forward"))

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
Thanks Biff,

tried it again and made sure no unseen characters and it seems
to work. Cheers.

:

I have 2 separate columns and I am trying to count the nu,ber
of occurances
for a 2 different values. i.e. how many times admin column C
and sick Column
D show up. Tried the SUMPRODUCT and it doesn't seem to work.
I get a value of
0 when there should be 3.

:

=SUMPRODUCT((A1:A100>="A")*(A1:A100<="D")*(B1:B100="psychiatrist"))
--
David Biddulph

"Nick Brunetti" <NickBrunetti@discussions.microsoft.com>
wrote in message
I am trying to count a cell if criteria from two separate
columns are
met.

For example, my first column has the letters A, B, C, D or
no letters
at
all. The second column has different descriptive words
(i.e. internal
medicine, psychiatrist, etc). I would like a cell to be
counted if a
cell
in
the first column contains an A or B or C or D AND a cell in
the second
colum
contains the word "psychiatrist".

What is the best function/formula to use for this problem?

:

=COUNTIF(A1:A100,"*@*")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be
gmail in
my
addy)



message
Aha... Sumproduct is not a function I ever tried

I have a column of text. Some cells contain the
character "@" with a
space
preceding the rest of text in the cell. I realize the @
could be
considered
an operator so it is preceded with an apostrophe to
designate text. I
want
to sum the cells that have the "@"

Thanks

:

You'll need to be a bit clearer as to what you're trying
to do.
If
COUNTIF
or SUMIF doesn't do the job, you may want to try
SUMPRODUCT. --
David Biddulph

message
I have been trying to calculate a column of Text in
order to sum
contents
by
certain criteria

I have tried Count, CoutA and CountIF and cannot be
able to get
anything
to
work.

Am I barking up the wrong tree? Can this be done?

Thanks much.

 

[Richard Horn]

Bingo! David, you are a genius.

thanks so much for your expertise and patience.

Richard

OK, now that you realise that to get the right answer you must ask the right
question, let's go back a few days.

Try changing
=SUMPRODUCT(('Q2'!D833="chris craig")*('Q2'!E8:E33<>"completed, carried
forward"))
to
=SUMPRODUCT(('Q2'!D833="chris
craig")*('Q2'!E8:E33<>"completed")*('Q2'!E8:E33<>"carried forward"))
--
David Biddulph

Oh my gosh! David I am so sorry.

The cell I am quering does not contain "completed, carried forward"

The cell would contain either "completed" or "carried forward". It
would be one or the otther.

I wanted to capture who the project lead is in column D, "chris
craig" and any project she is working on, unless the status is
"completed" or "carried forward" in column E. If the column E entry
is "completed" or "carried forward" I do not want that in the count.

Again, I apologize for not being so precise, as I know when making a
formula in Excel, you must be spot on.

richard

As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested
earlier) that the content of your cell is not "completed, carried
forward", because if you copy the string "completed, carried
forward" [without the quotes] into the cell and use the LEN function
on that you will see that it IS 26 characters.
What does =LEN('Q2'!E8) return? How many characters are in the
cell, if it isn't 26?
What does =CODE(RIGHT(A8)) return? It will be 100 if the last
character in the cell is "d". It will be 32 if the last character
is a space, and 160 if it is a non-breaking space.
--
David Biddulph

Richard Horn wrote:
Yes David, that's affirmative. I am certainily stuck.

I tried your 3 suggestions:
1. =('Q2'!E8<>"completed, carried forward") which returned all TRUE
even though column does contain a Completed status.
2. =('Q2'!E8="completed, carried forward") which returned all FALSE
even though column does contain a Completed status.
3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does
contain a Completed status.

Each of your suggested helpers I pasted in a cell, then copied them
down to cover a range of cells I wanted to test query.

Not unless I am totally misunderstanding what your trying to help me
with, I am not sure how to make this work, but I certainly
appreciate your patience is trying to share your experience.

thanks Richard

:

So you've confirmed that you have a problem, and that your formula
doesn't match your data, but you've not bothered with my other
suggestions? --
David Biddulph

Richard Horn wrote:
With the helper column I copied down, it did not return as it
should:

Whether I used =('Q2'!E8<>"completed, carried forward") which
returned all TRUE even though column does contain a Completed
status.

or

=('Q2'!E8="completed, carried forward") which returned all FALSE
even though column does contain a Completed status.

For column E on my Q2 worksheet I am using a Validation, List,
from the bottom of my sheet, so the project lead can have a
drop-down box to select their current status. So there would not
be any abnormal characters or spaces when I am trying to comprise
my formula.

:

That probably means that you've got some slight difference in the
content of column E, perhaps spare spaces or other characters
(such as non-breaking spaces) in the cell.

You can check with a helper column:
=('Q2'!E8<>"completed, carried forward") and copy down
and/or
=('Q2'!E8="completed, carried forward") and copy down.

You might also want to try =LEN('Q2'!E8)=26

You might wish to copy the string (which you think is
="completed, carried forward") from a suitable cell in column E
into your formula. --
David Biddulph

message
I found these comments very helpful so far. Now I am stumped.

I have a workbook with multiple worksheets and I am making a
summary page up
front for management review.

Here's what I am trying to do. From worksheet named Q2, I have
all my projects leads in column D, and then the current status
of their projects in
column E, for instance, intake, editing, pending, etc.. What I
am trying to
do is count all the instances of one project lead, in this case
chris craig,
and then I want to know all projects she is working on, unless,
or except, if
the status is completed or carried forward. If the status for a
project lead
is completed or carried, then do not count.

This is the formula I am trying to use, but is is counting all
projects for
the given lead with all statuses, including completed and
carried forward.

=SUMPRODUCT(('Q2'!D833="chris
craig")*('Q2'!E8:E33<>"completed, carried forward"))

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
Thanks Biff,

tried it again and made sure no unseen characters and it seems
to work. Cheers.

:

I have 2 separate columns and I am trying to count the nu,ber
of occurances
for a 2 different values. i.e. how many times admin column C
and sick Column
D show up. Tried the SUMPRODUCT and it doesn't seem to work.
I get a value of
0 when there should be 3.

:

=SUMPRODUCT((A1:A100>="A")*(A1:A100<="D")*(B1:B100="psychiatrist"))
--
David Biddulph

"Nick Brunetti" <NickBrunetti@discussions.microsoft.com>
wrote in message
I am trying to count a cell if criteria from two separate
columns are
met.

For example, my first column has the letters A, B, C, D or
no letters
at
all. The second column has different descriptive words
(i.e. internal
medicine, psychiatrist, etc). I would like a cell to be
counted if a
cell
in
the first column contains an A or B or C or D AND a cell in
the second
colum
contains the word "psychiatrist".

What is the best function/formula to use for this problem?

:

=COUNTIF(A1:A100,"*@*")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be
gmail in
my
addy)



message
Aha... Sumproduct is not a function I ever tried

I have a column of text. Some cells contain the
character "@" with a
space
preceding the rest of text in the cell. I realize the @
could be
considered
an operator so it is preceded with an apostrophe to
designate text. I
want
to sum the cells that have the "@"

Thanks

:

You'll need to be a bit clearer as to what you're trying
to do.
If
COUNTIF
or SUMIF doesn't do the job, you may want to try
SUMPRODUCT. --
David Biddulph

message
I have been trying to calculate a column of Text in
order to sum
contents
by
certain criteria

I have tried Count, CoutA and CountIF and cannot be
able to get
anything
to
work.

Am I barking up the wrong tree? Can this be done?

Thanks much.

 

[mohamed saeed algomrokshy]

I have been trying to calculate a column of Text in order to sum contents by
certain criteria

I have tried Count, CoutA and CountIF and cannot be able to get anything to
work.

Am I barking up the wrong tree? Can this be done?

Thanks much.

 

[LadyJags]

I need to count the number of text characters per cell to ensure we don't go
over 34 characters a line for major print jobs. Each line will be in a
different cell. How can I do this?

 

[Gord Dibben]

Characters per cell =LEN(cellref)

All that does is tell you how many chars per cell including spaces.

Do you want to automatically limit the numbers of chars to a maximum of 34?

You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) > 34 Then
.Value = Left(.Value, 34)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module, Edit the range to suit. Alt + q to return to
the Excel window.


Gord Dibben MS Excel MVP

 

[Antonella]

Hi,

I’m hoping that someone can help me..
I am trying to count how many export has been done for each Region for July,
how many for August and so on.
I’ll try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands, Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
In both columns data must be chosen from a drop down menu..
Hope I made myself clear.. Can this be done ?
Thanks
Antonella

 

[Antonella]

Hi,

I’m hoping that someone can help me..
I am trying to count how many export has been done for each Region for July,
how many for August and so on.
I’ll try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands, Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
In both columns data must be chosen from a drop down menu..
Hope I made myself clear.. Can this be done ?
Thanks
Antonella

 

[Antonella]

Hi,

I’m hoping that you can help me..
I am trying to count how many export has been done for each Region for July,
how many for August and so on.
I’ll try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands, Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
Hope I made myself clear.. Can this be done ?
I've tried COUNTIF but did not work. How can I nest COUNTIF and AND function?
Thanks

 

[jamescox]

See the attached workbook.

There may be an easier way, but by using the DCOUNTA function, you ca
define a critera range that can handle multiple criteria.

Hope this helps! :Bg

+-------------------------------------------------------------------
|Filename: Book2.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=181
+-------------------------------------------------------------------

 

[Ragdyer]

Sumproduct is the function that you'll want to use.

However, the proper configuration of the arguments in that function will
depend on *exactly* how the months are being entered into Column B.

Are the names of the months TEXT entries, OR, are they XL recognized dates,
formatted to display just the month name?

For months entered as text in Column B:
In C1 type in the name of the Region,
and in C2 type in the name of the month,
then try this:

=Sumproduct((A1:A100=C1)*(B1:B100=C2))

For months entered as "legal" XL dates in Column B,
try this:

=SUMPRODUCT((A1:A100=C1)*(TEXT(B1:B100,"mmmm")=C2))

 

[jamescox]

A workbook illustrating a solution to your requirements was posted as
reply to one of your previous posts with the same subject line.

Please check it out and, if it doesn't fit your needs, expand you
definition of the problem...

 

[Michael.Tarnowski]

A workbook illustrating a solution to your requirements was posted as a
reply to one of your previous posts with the same subject line.

Please check it out and, if it doesn't fit your needs, expand your
definition of the problem....

Hi,
for an alternative to SUMPRODUCT have a look at http://sulprobil.com/html/listfreq.html,
maybe you find help there.

Have fun, cheers
Michael

 

[JustMe]

I agree!!! Bob...you are a WONDERFUL man! I too could not figure out why it
was not working until I added the *'s in. Worked like a charm! God
bless!

 

[conner34]

David
I have a similar problem where I am tring to count the occurance of a text
in one column and a number value in a second column. For instance conlum A
contains 'Y' or 'N' and column C contians numbers 1, 2, 3, etc. So I want to
count the number of occurances where column A is 'Y' and column C is '3'.
Can you help with this?

Thanks
conner34

 

[Fred Smith]

=sumproduct(--(a1:a1000="Y"),--(c1:c1000=3))
Adjust the ranges to suit.

Regards,
Fred