Can I Use a Count Function for Text?

Joan, I have the @ character in the same cell more than once and when I use
the =COUNTIF(A1:A100,"*@*") that Bob Phillips recommended, I get a count of
five instead of 10 in my test text. For example, ,
on five different rows in Excel should count 10 @ chars.

"Joan NYC" wrote:

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.

=countif() will count the number of cells that match the criteria.

If you want to count the number of @'s in a range:
=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"@","")))

Adjust the range to match, but you can't use the entire column until xl2007.



Paula Ohio wrote:

Joan, I have the @ character in the same cell more than once and when I use
the =COUNTIF(A1:A100,"*@*") that Bob Phillips recommended, I get a count of
five instead of 10 in my test text. For example, ,
on five different rows in Excel should count 10 @ chars.

"Joan NYC" wrote:

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.




--

Dave Peterson

You may want to use pivot charts. First you create a colum (to count) put a
value of 1 in that colum for all the 1000. Next, with your curser within your
worksheet, go to the Data and select the pivot tables. Follow the steps. It
will ask you whether to create the pivot in the same sheet or different
sheet. Choose different sheet. Next it will give you the option of organizing
the data the way you want. In the body, where it says data, put the count
variable which you generated. In the left hand colum put the job role. Pivot
table works beautifully, I just finished working on something similar to
yours. if this is not clear, go to the help menu and type in pivot tables,
they explain very well.

Hope it helps,
Nasreen

"Robert" wrote:

Hi Bob, I am hoping you can help me. I have a list of about 1000 employees
and column AC list their job role. i.e. Project Manager, Developer, etc. Is
there a way to get a count of how many people are in each role?

thanks,

robert

"Bob Phillips" wrote:

I think so, it is a wildcard.

--
---
HTH

Bob

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



"Joan NYC" wrote in message
...
Thanks Bob

I will try it

I guess the "*" is the answer!



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

Hi I am trying to figure out how to count all the blocks that have text in
them. I have tried =COUNTIF(D2113,"*") but it counts only *, and it says in
the help section that it should work. does any one have any Idea's?

On Oct 30, 11:35*am, madchan001
wrote:
Hi I am trying to figure out how to count all the blocks that have text in
them. I have tried =COUNTIF(D2113,"*") but it counts only *, and it says in
the help section that it should work. does any one have any Idea's?

=SUMPRODUCT(--(ISTEXT(D2113)))

I am trying to use a count funtion for text.
=COUNTIF(K225:X225,"LOA")--this one works equal 1 but I need it to equal 3

But I need it to include to more labels can you help me.
=COUNTIF(K225:X225,"LOA,TRN,VAC")

How about a nice macro
'===========
Option Compare Text
Sub counttextinROW()
mr = 2
fc = Range("K1").Column
lc = Range("z1").Column

For i = fc To lc
If Cells(mr, i) = "loa" _
Or Cells(mr, i) = "b" _
Or Cells(mr, i) = "c" Then
mCount = mCount + 1
End If
Next i

MsgBox mCount
End Sub
'=============
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LaTanya" wrote in message
news
I am trying to use a count funtion for text.
=COUNTIF(K225:X225,"LOA")--this one works equal 1 but I need it to equal 3

But I need it to include to more labels can you help me.
=COUNTIF(K225:X225,"LOA,TRN,VAC")

I work in a drawing office an di need to count drawings according to their
date issued and their revision, am i able to graphically show these resutls?
use countif etc to make a table to produce a chart from... before i waste
hours playing around i thought i would ask the qestion

thank you

Neil


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

I'm trying to do something similar. I need to count the number of apples in
column D. But i only need to count the ones in rows 25-35 and rows 50-75.
I've tried countif, sumif, and sumproduct. Is there a way to do this?

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

You could try this:

=COUNTIF(D2535,"apples")+COUNTIF(D5075,"apples ")

Hope this helps.

Pete

On Jan 13, 12:27*am, jolineachi
wrote:
I'm trying to do something similar. *I need to count the number of apples in
column D. *But i only need to count the ones in rows 25-35 and rows 50-75. *
I've tried countif, sumif, and sumproduct. *Is there a way to do this?



"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.- Hide quoted text -

- Show quoted text -