Can I Use a Count Function for Text?

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

"Pete_UK" wrote:

You could try this:

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

Hope this helps.

Pete

On Jan 13, 12:27 am, jolineachi
wrote:
I'm trying to do something similar. I need to count the number of apples in
column D. But i only need to count the ones in rows 25-35 and rows 50-75.
I've tried countif, sumif, and sumproduct. Is there a way to do this?



"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 - thanks for feeding back.

Pete

On Jan 13, 2:15*am, jolineachi
wrote:
Thanks Pete! *You're a life saver!!!

Hi,
I need some big help pls. Im working on a sheet and Its giving me sho much
error. 1. I want to be able to have a automatic respoance come up in another
cell depending on the answer giving in one from a drop down list. E.g. If
"Service Department" is selected in C2 then "John Doe" would come up in E2.
What I want is no matter what Department I select it would give me the
correct manger instead of me having to type in the names or look in list to
try match name. List would be pre done

2. Im also trying to have a count done based on if Yes, No or N/A is input
from drop down list. Rows 1-200 has information but I need to have a count to
be done automatically and transfered to another sheet in same workbook with
the total amount of Yes, No or N/A.

3. Finally, say I select Service Deparment (which has 10 different job
position) from drop down list in C2, to have correct list of positions appear
in drop down list for D2 and depending on which position is selected in D2
correct list of Employees is in drop down list in E2.

thanks alot if anyone can help me. It would really ease my pain.

"T. Valko" wrote:

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.

1. Use a VLOOKUP formula with a two column lookup table on another sheet.

2. =COUNTIF(Sheet1!A1:A200,"Yes") or "No" or "#N/A"

3. See Debra Dalgleish's site for dependent dropdowns.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

On Mon, 19 Jan 2009 13:06:04 -0800, djs
wrote:

Hi,
I need some big help pls. Im working on a sheet and Its giving me sho much
error. 1. I want to be able to have a automatic respoance come up in another
cell depending on the answer giving in one from a drop down list. E.g. If
"Service Department" is selected in C2 then "John Doe" would come up in E2.
What I want is no matter what Department I select it would give me the
correct manger instead of me having to type in the names or look in list to
try match name. List would be pre done

2. Im also trying to have a count done based on if Yes, No or N/A is input
from drop down list. Rows 1-200 has information but I need to have a count to
be done automatically and transfered to another sheet in same workbook with
the total amount of Yes, No or N/A.

3. Finally, say I select Service Deparment (which has 10 different job
position) from drop down list in C2, to have correct list of positions appear
in drop down list for D2 and depending on which position is selected in D2
correct list of Employees is in drop down list in E2.

thanks alot if anyone can help me. It would really ease my pain.

"T. Valko" wrote:

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.

I have another question:

I need to find the number of cases that were "Dismissed" in Column C between
"01/01/08" and "12/31/08" in Column D. The formula I tried was

=SUMPRODUCT(D149160="Guilty")*(E149:E160="01/01/07")*(E149:E160="12/31/07")

I keep getting 0 when I should get 3. Can anyone help me?

"jolineachi" wrote:

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

"Pete_UK" wrote:

You could try this:

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

Hope this helps.

Pete

On Jan 13, 12:27 am, jolineachi
wrote:
I'm trying to do something similar. I need to count the number of apples in
column D. But i only need to count the ones in rows 25-35 and rows 50-75.
I've tried countif, sumif, and sumproduct. Is there a way to do this?



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

The formula I tried was
=SUMPRODUCT(D149160="Guilty")*(E149:E160="01/01/07")*(E149:E160="12/31/07")

That doesn't match your description at all!

Try it like this:

=SUMPRODUCT(--(D149160="Dismissed"),--(E149:E160=DATE(2008,1,1)),--(E149:E160=DATE(2008,12,31)))

Better to use cells to hold the criteria:

A1 = Dismissed
B1 = start date
C1 = end date

=SUMPRODUCT(--(D149160=A1),--(E149:E160=B1),--(E149:E160=C1))

Or, if your time period is for the entire specific year:

=SUMPRODUCT(--(D149160=A1),--(YEAR(E149:E160)=2008))


--
Biff
Microsoft Excel MVP


"jolineachi" wrote in message
...
I have another question:

I need to find the number of cases that were "Dismissed" in Column C
between
"01/01/08" and "12/31/08" in Column D. The formula I tried was

=SUMPRODUCT(D149160="Guilty")*(E149:E160="01/01/07")*(E149:E160="12/31/07")

I keep getting 0 when I should get 3. Can anyone help me?

"jolineachi" wrote:

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

"Pete_UK" wrote:

You could try this:

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

Hope this helps.

Pete

On Jan 13, 12:27 am, jolineachi
wrote:
I'm trying to do something similar. I need to count the number of
apples in
column D. But i only need to count the ones in rows 25-35 and rows
50-75.
I've tried countif, sumif, and sumproduct. Is there a way to do
this?



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

I have another question:

I need to find the number of cases that were "Dismissed" in Column C between
"01/01/08" and "12/31/08" in Column D. The formula I tried was

=SUMPRODUCT(D149160="Guilty")*(E149:E160="01/01/07")*(E149:E160="12/31/07")

I keep getting 0 when I should get 3. Can anyone help me?

"Pete_UK" wrote:

You're welcome - thanks for feeding back.

Pete

On Jan 13, 2:15 am, jolineachi
wrote:
Thanks Pete! You're a life saver!!!

Sorry! The formula is supposed to read "Dismissed". Does anyone know how to
delete a post?

Thanks T.Valko! The first formula is great for when I compile my semi-annual
reports and the third formula is perfect for my annual reports. Thanks a
bunch!!!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jolineachi" wrote in message
...
Thanks T.Valko! The first formula is great for when I compile my
semi-annual
reports and the third formula is perfect for my annual reports. Thanks a
bunch!!!