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

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")*(D1100="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.

I just found your discussion group. I also have two columns of information
and when I post the formula it simply appears as an entry. The cell where I
posted the formula is a text cell. What should the cell format be? Sorry!
This is all so new to me.
--
ldmci


"Steve Scatt" wrote:

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.