Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Can I Use a Count Function for Text?

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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Can I Use a Count Function for Text?

Hi there! Yes, you can definitely use a count function for text in Excel. However, the specific function you'll want to use depends on what you're trying to accomplish.

If you're trying to count the number of cells in a range that contain text (regardless of what the text says), you can use the COUNTA function. Here's how:
  1. Select the range of cells you want to count.
  2. In an empty cell, type "
    Formula:
    =COUNTA(range
    " (without the quotes), where "range" is the range of cells you selected in step 1.
  3. Press Enter.

The cell you typed the formula into should now display the number of cells in the range that contain text.

If you're trying to count the number of cells in a range that contain specific text (e.g. "apple"), you can use the COUNTIF function. Here's how:
  1. Select the range of cells you want to count.
  2. In an empty cell, type "
    Formula:
    =COUNTIF(range,"text"
    " (without the quotes), where "range" is the range of cells you selected in step 1, and "text" is the specific text you want to count.
  3. Press Enter.

The cell you typed the formula into should now display the number of cells in the range that contain the specific text you specified.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Can I Use a Count Function for Text?

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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Can I Use a Count Function for Text?

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.




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Can I Use a Count Function for Text?

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









  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Can I Use a Count Function for Text?

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.







  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Can I Use a Count Function for Text?

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.









  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Can I Use a Count Function for Text?

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


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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Can I Use a Count Function for Text?

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.







  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Can I Use a Count Function for Text?

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











  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default Can I Use a Count Function for Text?

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









  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
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.










  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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")*(D2:D20,"<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.










  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Can I Use a Count Function for Text?

I think you need this:

=SUMPRODUCT((A2:A20="reason")*(D2:D20<""))

The second term means cells in D2:D20 are not blank.

Your second question can be achieved like this:

=SUMPRODUCT((A2:A20="reason")*(B2:B200)*(B2:B20<1 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")*(D2:D20,"<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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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")*(D2:D20<""))

The second term means cells in D2:D20 are not blank.

Your second question can be achieved like this:

=SUMPRODUCT((A2:A20="reason")*(B2:B200)*(B2:B20<1 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")*(D2:D20,"<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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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")*(D2:D20<""))

The second term means cells in D2:D20 are not blank.

Your second question can be achieved like this:

=SUMPRODUCT((A2:A20="reason")*(B2:B200)*(B2:B20<1 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")*(D2:D20,"<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 -



  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
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..


  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 334
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???


  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
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???




  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 334
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???







  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
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???







  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default I need to count a row with something in it

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

"T. Valko" wrote:

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








  #23   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default I need to count a row with something in it

It's not clear what you want. Can you provide a more detailed explanation?


--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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










  #24   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default I need to count a row with something in it

A100
1B00
10C0
D100
=
400
I need to add the rows the problem that I am running into is that it does
not recognize the number because of the alpha included in the number
I hope this explains it a little better


"T. Valko" wrote:

It's not clear what you want. Can you provide a more detailed explanation?


--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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











  #25   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default I need to count a row with something in it

As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.

With original data starting in A1,
try this in B1:

=IF(A10,--REPLACE(A1,MIN(SEARCH(
{"a","b","c","d","e","f","g","h","i","j","k","l"," m","n","o","p","q","r","s","t","u","v","w","x","y" ,"z"},
A1&"abcdefghijklmnopqrstuvwxyz")),1,""),"")

And copy down as needed.
Then Sum Column B.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elvira" wrote in message
...
A100
1B00
10C0
D100
=
400
I need to add the rows the problem that I am running into is that it does
not recognize the number because of the alpha included in the number
I hope this explains it a little better


"T. Valko" wrote:

It's not clear what you want. Can you provide a more detailed
explanation?


--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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















  #26   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default I need to count a row with something in it

Thank you IT WORKS!! ; 0)

Youre awesome!!

"RagDyer" wrote:

As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.

With original data starting in A1,
try this in B1:

=IF(A10,--REPLACE(A1,MIN(SEARCH(
{"a","b","c","d","e","f","g","h","i","j","k","l"," m","n","o","p","q","r","s","t","u","v","w","x","y" ,"z"},
A1&"abcdefghijklmnopqrstuvwxyz")),1,""),"")

And copy down as needed.
Then Sum Column B.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elvira" wrote in message
...
A100
1B00
10C0
D100
=
400
I need to add the rows the problem that I am running into is that it does
not recognize the number because of the alpha included in the number
I hope this explains it a little better


"T. Valko" wrote:

It's not clear what you want. Can you provide a more detailed
explanation?


--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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














  #27   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default I need to count a row with something in it

You're welcome, and thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elvira" wrote in message
...
Thank you IT WORKS!! ; 0)

You're awesome!!

"RagDyer" wrote:

As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.

With original data starting in A1,
try this in B1:

=IF(A10,--REPLACE(A1,MIN(SEARCH(
{"a","b","c","d","e","f","g","h","i","j","k","l"," m","n","o","p","q","r","s","t","u","v","w","x","y" ,"z"},
A1&"abcdefghijklmnopqrstuvwxyz")),1,""),"")

And copy down as needed.
Then Sum Column B.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elvira" wrote in message
...
A100
1B00
10C0
D100
=
400
I need to add the rows the problem that I am running into is that it
does
not recognize the number because of the alpha included in the number
I hope this explains it a little better


"T. Valko" wrote:

It's not clear what you want. Can you provide a more detailed
explanation?


--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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
















  #28   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default I need to count a row with something in it

"RagDyer" wrote...
As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.

With original data starting in A1,
try this in B1:

=IF(A10,--REPLACE(A1,MIN(SEARCH({"a","b","c","d","e","f",

"g","h","i","j","k","l","m","n","o","p","q","r","s ","t","u",
"v","w","x","y","z"},A1&"abcdefghijklmnopqrstuvwx yz")),1,""),"")

....

Alternatively, if there'd never be more than 6 characters and all
numbers would be positive integers, you could do it with a single
formula.

=SUMPRODUCT(--(MID(rng,1,--ISNUMBER(-MID(rng,1,1)))
&MID(rng,2,--ISNUMBER(-MID(rng,2,1)))
&MID(rng,3,--ISNUMBER(-MID(rng,3,1)))
&MID(rng,4,--ISNUMBER(-MID(rng,4,1)))
&MID(rng,5,--ISNUMBER(-MID(rng,5,1)))
&MID(rng,6,--ISNUMBER(-MID(rng,6,1)))))

where rng is a placeholder for the range in question.
  #29   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kev Kev is offline
external usenet poster
 
Posts: 46
Default Can I Use a Count Function for Text?

Hi,

Can anyone please help, i'm trying to count the total number of occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont seem
to be working - any suggestions
thanks
  #30   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Can I Use a Count Function for Text?

Maybe this:

=SUMPRODUCT(--(B1:B10="O"),--(C1:C10="house"))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
Hi,

Can anyone please help, i'm trying to count the total number of
occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont
seem
to be working - any suggestions
thanks





  #31   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Can I Use a Count Function for Text?

I am trying to use the count function on text in a totally different way...

I want to put the word DUPE or some type of flag in one column based on
whether or not COUNTIF on a different column is greater than 1

So instead of having to eyeball a column to look for formatted columns
indicating that a conditional formula has been met, i'd like a way to use the
autofilter tool on a different column so that only (and all) rows with
duplicates display .. so i can work with just those.

thanks.
  #32   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 622
Default Can I Use a Count Function for Text?

On Jul 17, 11:17 pm, beckyd wrote:
I am trying to use the count function on text in a totally different way...

I want to put the word DUPE or some type of flag in one column based on
whether or not COUNTIF on a different column is greater than 1

So instead of having to eyeball a column to look for formatted columns
indicating that a conditional formula has been met, i'd like a way to use the
autofilter tool on a different column so that only (and all) rows with
duplicates display .. so i can work with just those.

thanks.

So, something like this. You'll have to modify the COUNTIF part.
=IF(COUNTIF($C$3:$C$100,C3)1,"DUPE","")
  #33   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kev Kev is offline
external usenet poster
 
Posts: 46
Default Can I Use a Count Function for Text?

thanks for your help!!

"T. Valko" wrote:

Maybe this:

=SUMPRODUCT(--(B1:B10="O"),--(C1:C10="house"))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
Hi,

Can anyone please help, i'm trying to count the total number of
occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont
seem
to be working - any suggestions
thanks




  #34   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Can I Use a Count Function for Text?

You're welcome!

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
thanks for your help!!

"T. Valko" wrote:

Maybe this:

=SUMPRODUCT(--(B1:B10="O"),--(C1:C10="house"))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
Hi,

Can anyone please help, i'm trying to count the total number of
occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont
seem
to be working - any suggestions
thanks






  #35   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Can I Use a Count Function for Text?

Hello,
I need to count how many times does a word "ITS" appear in my column. the
problem is that it appears multiple times within the same cell but excel
counts that cell just once instead of lets say 5 times when ITS appears 5
times in the cell. and also, my list is filtered so it looks like excel is
including the missing lines as well which i dont need.

thank you


  #36   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default Can I Use a Count Function for Text?

One way, using a helper column.

1) In an unused column (say "H") enter

H1: =IF(SUBTOTAL(103,A1),A1)

and copy down as far as required, say, H1000. Hide the column.

2) In your target cell, array enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(H1:H1000),-LEN(SUBSTITUTE(H1:H1000,"its","")))/LEN("its")


In article ,
Sedmikraska wrote:

Hello,
I need to count how many times does a word "ITS" appear in my column. the
problem is that it appears multiple times within the same cell but excel
counts that cell just once instead of lets say 5 times when ITS appears 5
times in the cell. and also, my list is filtered so it looks like excel is
including the missing lines as well which i dont need.

thank you

  #37   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Can I Use a Count Function for Text?

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.










  #38   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Can I Use a Count Function for Text?

Does your formula look something like this:

=SUMPRODUCT((C1:C100="admin")*(D1:D100="sick"))

If you get a result of 0 then that means there are no matches. There may be
leading/traiking spaces or other unseen characters causing this. For
example:

_admin
admin_
_sick
sick_

Where the underscore represents an unseen character. Check your data and
make the necessary corrections.


--
Biff
Microsoft Excel MVP


"Steve Scatt" <Steve wrote in message
...
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.












  #39   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Can I Use a Count Function for Text?

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.










  #40   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Can I Use a Count Function for Text?

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.












Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count single Text in cells with multiple text entries WSC Excel Discussion (Misc queries) 6 January 9th 07 05:17 PM
how do i count text and display it as text plus the # times it hap Count in Excel New Users to Excel 1 April 26th 06 10:15 AM
how do I count the number of times text in column A matches text i Sheila Excel Worksheet Functions 2 November 16th 05 11:20 PM
Formula to count text and alert me if a text appears more than twi Mike Excel Discussion (Misc queries) 1 August 29th 05 09:53 PM
Count Intervals of Filtered TEXT values in Column and Return Count across a Row Sam via OfficeKB.com Excel Worksheet Functions 9 July 31st 05 03:37 AM


All times are GMT +1. The time now is 09:58 PM.

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

About Us

"It's about Microsoft Excel"