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

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.

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

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.

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.

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

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.

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

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

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

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 -

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 -

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 -

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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.

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.

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.

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.