Can I Use a Count Function for Text?

[Cathy M]

eI am trying to put in the formula :
=COUNTIF(c4:c10, "cashiering")
to count recurrently rows of information.

The formula stays in the cell but the number does not materialize. I have
tried reformatting the cells to reflect number or general to no avail.

What am I doing wrong?

Cathy M

 

[T. Valko]

Format the cell as General.
With the cell still selected hit function key F2 then hit ENTER.

 

[TP]

you saved my life, thanks for the post, my mgr will be pleased with the next
wave of spreadsheets

 

[kbrane]

Hello,
I also just found the discussion group and this is probably an easy fix, but
everything I try give me an error.
I have a column of RSVP's....so in each cell I have either a "yes" or a
"no". I want a formula that will count the number of "yes"es so I know how
many people are coming to an event. I don't want to have to count by hand,
nor do I want to assign a value for each kind of response. I want Excel to
simply count "yes". Make sense?

kbrane

 

[Gord Dibben]

=COUNTIF(A:A,"yes")

I wouldn't bother counting "no" because they aren't coming so you don't need
a knife and fork for them.


Gord Dibben MS Excel MVP

 

[Chgrec]

I have a similar problem I want to see how many of each text item are in a
column.
There only a dozen or so unique words out of a column of 450 rows but
that text is not static so I cant just use =COUNTIF(c4:c10, "text") as the
text value will change each month

Is there a way to get use the countif formula to read each cell text entry
and give me a total count of each text?

Thanks

 

[Chgrec]

Just to clarify. If I have a column that looks like this:
|Biscuits |
|Biscuits |
|Biscuits |
|Biscuits |
|Potatoes |
|Potatoes |
|Potatoes |
|Beef |
|Beef |
|Beef |
|Beef |
|Wine |

I want a result like this:
Wine = 1
Beef = 4
Potatoes = 3
Biscuits = 4

So that I dont have to type in Wine or beef in my formula. I need that
variable to be calculated and entered automatically. I know I can do it in
SQL but I dont want to have to create a database just for this....

Thanks

 

[Glenn]

Use a PivotTable.

Just to clarify. If I have a column that looks like this:
|Biscuits |
|Biscuits |
|Biscuits |
|Biscuits |
|Potatoes |
|Potatoes |
|Potatoes |
|Beef |
|Beef |
|Beef |
|Beef |
|Wine |

I want a result like this:
Wine = 1
Beef = 4
Potatoes = 3
Biscuits = 4

So that I dont have to type in Wine or beef in my formula. I need that
variable to be calculated and entered automatically. I know I can do it in
SQL but I dont want to have to create a database just for this....

Thanks

 

[Chgrec]

That is exactly what I neded, Thanks!!

Chris

Use a PivotTable.

 

[Paula Ohio]

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, (e-mail address removed),
(e-mail address removed) on five different rows in Excel should count 10 @ chars.

 

[Dave Peterson]

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

 

[Nasreen]

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

 

[madchan001]

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?

 

[Spiky]

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

 

[LaTanya]

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

 

[Don Guillett]

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
'=============

 

[Nellydotcom]

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

 

[jolineachi]

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?

 

[Pete_UK]

You could try this:

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

Hope this helps.

Pete

 

[jolineachi]

Thanks Pete! You're a life saver!!!

You could try this:

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

Hope this helps.

Pete