Can I Use a Count Function for Text?

I have a similar problem but your suggestion isn't working for me.

I have two columns of data that I want to count, but I only want to count
the intersection of the two columns. So, if A1 and B1 both contain the same
text I only want to count it once. Is that possible to do?

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve Scatt" wrote in message
...
Thanks Biff,

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

"Steve Scatt" wrote:

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.

"David Biddulph" wrote:

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

"Nick Brunetti" 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?

"Bob Phillips" wrote:

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

--
---
HTH

Bob

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



"Joan NYC" wrote in 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

"David Biddulph" wrote:

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

"Joan NYC" wrote in 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.

Maybe this:

=SUMPRODUCT(--(A1:A5=B1:B5))

...........A..........B
1........x...........x
2........y...........z
3........c...........c
4........s...........s
5........v..........w

Based on that sample data the result would be 3.

--
Biff
Microsoft Excel MVP


"Nichole Beck" Nichole wrote in message
...
I have a similar problem but your suggestion isn't working for me.

I have two columns of data that I want to count, but I only want to count
the intersection of the two columns. So, if A1 and B1 both contain the
same
text I only want to count it once. Is that possible to do?

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve Scatt" wrote in message
...
Thanks Biff,

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

"Steve Scatt" wrote:

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.

"David Biddulph" wrote:

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

"Nick Brunetti" 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?

"Bob Phillips" wrote:

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

--
---
HTH

Bob

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



"Joan NYC" wrote in 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

"David Biddulph" wrote:

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

"Joan NYC" wrote in
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.

"Joan NYC" wrote:

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.

"Joan NYC" wrote:

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.

Need more information. Can you provide an exampleo of what you're
trying to do?


--
Christopher770
------------------------------------------------------------------------
Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74543

When you say calculate, do you mean SUM rather than COUNT?gaelf;267108 Wrote:
"Joan NYC" wrote:

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.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74543

Here is my question:

How can I use a formula to create the following:

I want one column to be three-five different text (i.e. W, L, D, etc..) and
the next column to convert that text into a number (10, 5, 0, etc..)

I have been trying to figure this out all day, and any help is greatly
appreciated.

Thank you,

=LOOKUP(A1,{"D","L","W"},{0,5,10}) entered in B1

Note the lookup_vector {"D","L","W"} must be in ascending order.


Gord Dibben MS Excel MVP


On Wed, 22 Apr 2009 13:29:02 -0700, Davina
wrote:

Here is my question:

How can I use a formula to create the following:

I want one column to be three-five different text (i.e. W, L, D, etc..) and
the next column to convert that text into a number (10, 5, 0, etc..)

I have been trying to figure this out all day, and any help is greatly
appreciated.

Thank you,

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"))

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve Scatt" wrote in message
...
Thanks Biff,

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

"Steve Scatt" wrote:

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.

"David Biddulph" wrote:

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

"Nick Brunetti" 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?

"Bob Phillips" wrote:

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

--
---
HTH

Bob

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



"Joan NYC" wrote in 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

"David Biddulph" wrote:

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

"Joan NYC" wrote in 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.

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

"Richard Horn" Richard wrote in 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"))

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve Scatt" wrote in message
...
Thanks Biff,

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

"Steve Scatt" wrote:

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.

"David Biddulph" wrote:

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

"Nick Brunetti" 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?

"Bob Phillips" wrote:

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

--
---
HTH

Bob

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



"Joan NYC" wrote in 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

"David Biddulph" wrote:

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

"Joan NYC" wrote in
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.