A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can I Use a Count Function for Text?



 
 
Thread Tools Display Modes
  #11  
Old June 26th, 2008, 12:00 AM posted to microsoft.public.excel.worksheet.functions
Robert
external usenet poster
 
Posts: 717
Default Can I Use a Count Function for Text?

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.










  #12  
Old June 29th, 2008, 01:41 PM posted to microsoft.public.excel.worksheet.functions
O2 andy
external usenet poster
 
Posts: 1
Default Can I Use a Count Function for Text?

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



"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

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










  #13  
Old June 29th, 2008, 02:26 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default Can I Use a Count Function for Text?

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:B200)*(B2:B201 4))

Hope this helps.

Pete

On Jun 29, 1:41*pm, O2 andy O2 wrote:
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



"Bob Phillips" wrote:
=SUMPRODUCT((A2:A20={"A","B","C","D"})*(B2:B20="ps ychiatrist"))


--
__________________________________
HTH


Bob


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


- Show quoted text -


  #14  
Old June 29th, 2008, 02:33 PM posted to microsoft.public.excel.worksheet.functions
O2 andy[_2_]
external usenet poster
 
Posts: 6
Default Can I Use a Count Function for Text?

Great, thanks..

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


"Pete_UK" wrote:

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:B200)*(B2:B201 4))

Hope this helps.

Pete

On Jun 29, 1:41 pm, O2 andy O2 wrote:
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



"Bob Phillips" wrote:
=SUMPRODUCT((A2:A20={"A","B","C","D"})*(B2:B20="ps ychiatrist"))


--
__________________________________
HTH


Bob


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


- Show quoted text -



  #15  
Old June 29th, 2008, 03:30 PM posted to microsoft.public.excel.worksheet.functions
O2 andy[_2_]
external usenet poster
 
Posts: 6
Default Can I Use a Count Function for Text?

Worked a treat Pete..

Thanks again.....

"O2 andy" wrote:

Great, thanks..

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


"Pete_UK" wrote:

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:B200)*(B2:B201 4))

Hope this helps.

Pete

On Jun 29, 1:41 pm, O2 andy O2 wrote:
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



"Bob Phillips" wrote:
=SUMPRODUCT((A2:A20={"A","B","C","D"})*(B2:B20="ps ychiatrist"))

--
__________________________________
HTH

Bob

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

- Show quoted text -



  #16  
Old June 29th, 2008, 03:40 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default Can I Use a Count Function for Text?

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

Pete

On Jun 29, 3:30*pm, O2 andy wrote:
Worked a treat Pete..

Thanks again.....



"O2 andy" wrote:
Great, thanks..


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


  #17  
Old July 2nd, 2008, 08:55 PM posted to microsoft.public.excel.worksheet.functions
Rick
external usenet poster
 
Posts: 727
Default I need to count a row with something in it

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


  #18  
Old July 2nd, 2008, 09:11 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default I need to count a row with something in it

Maybe this:

=COUNTA(D61:J61)

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

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
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???




  #19  
Old July 3rd, 2008, 01:04 PM posted to microsoft.public.excel.worksheet.functions
Rick
external usenet poster
 
Posts: 727
Default I need to count a row with something in it

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" wrote:

Maybe this:

=COUNTA(D61:J61)

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

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
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???





  #20  
Old July 3rd, 2008, 05:12 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default I need to count a row with something in it

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
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" wrote:

Maybe this:

=COUNTA(D61:J61)

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

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
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???







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:00 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.