Can I Use a Count Function for Text?

[Guest]

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.

 

[David Biddulph]

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.

 

[Guest]

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

 

[Bob Phillips]

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

--
---
HTH

Bob

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

 

[Guest]

Thanks Bob

I will try it

I guess the "*" is the answer!

 

[Bob Phillips]

I think so, it is a wildcard.

--
---
HTH

Bob

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

 

[catwoman48]

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

--
---
HTH

Bob

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

<<SMAK>>
You wonderful person! I just spent an hour and a half doing research on
this very thing. Couldn't figure out why my formula(s) wouldn't work. I had
"wildcard" in the back of my mind, but that seemed too simple, and didn't
make sense, as my search criterion was not a symbol. But it worked, and I
thank you a million times.

 

[Nick Brunetti]

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]

=SUMPRODUCT((A2:A20={"A","B","C","D"})*(B2:B20="psychiatrist"))

 

[Robert]

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

 

[O2 andy]

Hi all,

I'm looking to do something along the same lines but using text and numbers.
The formula below is what i've come up with

=SUMPRODUCT((A2:A20="reason")*(D220,"<0""))

I get a value of 0 or an error returning. I'm also looking to do a range
for the numbers too, so B2 to B20 for numbers greater than 0 but less than 14
and equal to reason.

Hope you can help.

Many thanks

Andy

 

[Pete_UK]

I think you need this:

=SUMPRODUCT((A2:A20="reason")*(D220<>""))

The second term means cells in D220 are not blank.

Your second question can be achieved like this:

=SUMPRODUCT((A2:A20="reason")*(B2:B20>0)*(B2:B20<14))

Hope this helps.

Pete

 

[O2 andy]

Great, thanks..

I'll give it a go and let you know how i get on..

 

[O2 andy]

Worked a treat Pete..

Thanks again.....

Great, thanks..

I'll give it a go and let you know how i get on..

 

[Pete_UK]

You're welcome, Andy - thanks for feeding back.

Pete

 

[Rick]

I have been following this strem but have not seen something that I need ....
I need to count a row (e.g., d61:j61) with anything in it - either a number
or word .... can one of you please help me???

 

[T. Valko]

Maybe this:

=COUNTA(D61:J61)

That will return the count of cells in the range that are not empty.

 

[Rick]

T. Valko..... You are the best..... this stuff is sooooo simple if you know
the basic rules ..... oh yea just like life
Thanks again!!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Elvira]

I have a simmilar problem. I need to count rows with combinations of letters
and alpha i.e. 45A111 + 45A222 should equal 90333. I need the formula to
exclude the numeric factor.

Please advice