Counting Text Strings
 

How do I count the number of occurrences of two text strings in a range? I
am compiling demographic information for my boss at work and need to know the
number of caucasion males, caucasion females, etc. I have already created
the array formulas to count one text string in a range (i.e. number of males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

=SUM(COUNTIF(A2:A100,{"string1","string2"}))



--
Regards,

Peo Sjoblom



"Kholm" wrote in message
...
How do I count the number of occurrences of two text strings in a range?
I
am compiling demographic information for my boss at work and need to know
the
number of caucasion males, caucasion females, etc. I have already created
the array formulas to count one text string in a range (i.e. number of
males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

I think we need more info
how is your data formatted?
is the info you are looking for the only thing in a given cell or is it only
part of the cell?
What array formula are you currently using?

"Kholm" wrote:

How do I count the number of occurrences of two text strings in a range? I
am compiling demographic information for my boss at work and need to know the
number of caucasion males, caucasion females, etc. I have already created
the array formulas to count one text string in a range (i.e. number of males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

I'm not very knowledgable with Excel so I'm not sure how my data is
formatted.

The info I am trying to count is in 2 separate columns. One column is
labeled sex and the other is race.

Here is the array formula I am using to count single strings of text.
=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")

"bj" wrote:

I think we need more info
how is your data formatted?
is the info you are looking for the only thing in a given cell or is it only
part of the cell?
What array formula are you currently using?

"Kholm" wrote:

How do I count the number of occurrences of two text strings in a range? I
am compiling demographic information for my boss at work and need to know the
number of caucasion males, caucasion females, etc. I have already created
the array formulas to count one text string in a range (i.e. number of males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

This formula is calculating the number of times white and male appears in the
range. However, I need to know how many times white and male appears
together in the range. Ex. the number of males who are also white.
How do I modify the formula to calculate this?

"Peo Sjoblom" wrote:


=SUM(COUNTIF(A2:A100,{"string1","string2"}))



--
Regards,

Peo Sjoblom



"Kholm" wrote in message
...
How do I count the number of occurrences of two text strings in a range?
I
am compiling demographic information for my boss at work and need to know
the
number of caucasion males, caucasion females, etc. I have already created
the array formulas to count one text string in a range (i.e. number of
males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

So are these part of other strings?

Also note that SUBSTITUTE is case sensitive so it won't count male (you can
wrap the cell references in LOWER and use "male" and all version will be
counted

Otherwise why don't you just use 2 formulas and add them

=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")+
SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Female","")))/LEN("Female")

also if you replace SUM with SUMPRODUCT you don't need to array enter the
formula


Of course if there are no other strings and your data looks like


Male
Male
Female
etc

you can simply use



=SUM(COUNTIF(A2:A100,{"Male","Female"}))

will do the job if there are no other strings in the cells



--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
I'm not very knowledgable with Excel so I'm not sure how my data is
formatted.

The info I am trying to count is in 2 separate columns. One column is
labeled sex and the other is race.

Here is the array formula I am using to count single strings of text.

=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")

"bj" wrote:

I think we need more info
how is your data formatted?
is the info you are looking for the only thing in a given cell or is it
only
part of the cell?
What array formula are you currently using?

"Kholm" wrote:

How do I count the number of occurrences of two text strings in a
range? I
am compiling demographic information for my boss at work and need to
know the
number of caucasion males, caucasion females, etc. I have already
created
the array formulas to count one text string in a range (i.e. number of
males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

So how are these setup? Are you using 2 columns or do you mean a single cell
can hold "White Male" (not a very good design to have it like that, next
time if possible use multiple cells)


=SUMPRODUCT(LEN(B2:B100)-LEN(SUBSTITUTE(LOWER(B2:B100),"white
male","")))/LEN("White Male")

might work



--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
This formula is calculating the number of times white and male appears in
the
range. However, I need to know how many times white and male appears
together in the range. Ex. the number of males who are also white.
How do I modify the formula to calculate this?

"Peo Sjoblom" wrote:


=SUM(COUNTIF(A2:A100,{"string1","string2"}))



--
Regards,

Peo Sjoblom



"Kholm" wrote in message
...
How do I count the number of occurrences of two text strings in a
range?
I
am compiling demographic information for my boss at work and need to
know
the
number of caucasion males, caucasion females, etc. I have already
created
the array formulas to count one text string in a range (i.e. number of
males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

I think so. Here is what my worksheet looks like.

Applicant Name Sex Race

John Doe Male White
Jane Doe Female Hispanic
James Doe Male African American

I need to be able to calculate how many people are both male and white. In
this example the answer is 1, but I don't know how to write the formula to do
this.

When I use 2 formulas and add them like you suggested it gives me an answer
of 3 as it is counting the number of times Male appears and the number of
times White appears.


I appreciate all your suggestions thus far and will greatly appreciate any
more you may have. I'm on a deadline to get this done for my boss and I'm
stuck.

Thanks.
"Peo Sjoblom" wrote:

So are these part of other strings?

Also note that SUBSTITUTE is case sensitive so it won't count male (you can
wrap the cell references in LOWER and use "male" and all version will be
counted

Otherwise why don't you just use 2 formulas and add them

=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")+
SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Female","")))/LEN("Female")

also if you replace SUM with SUMPRODUCT you don't need to array enter the
formula


Of course if there are no other strings and your data looks like


Male
Male
Female
etc

you can simply use



=SUM(COUNTIF(A2:A100,{"Male","Female"}))

will do the job if there are no other strings in the cells



--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
I'm not very knowledgable with Excel so I'm not sure how my data is
formatted.

The info I am trying to count is in 2 separate columns. One column is
labeled sex and the other is race.

Here is the array formula I am using to count single strings of text.

=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")

"bj" wrote:

I think we need more info
how is your data formatted?
is the info you are looking for the only thing in a given cell or is it
only
part of the cell?
What array formula are you currently using?

"Kholm" wrote:

How do I count the number of occurrences of two text strings in a
range? I
am compiling demographic information for my boss at work and need to
know the
number of caucasion males, caucasion females, etc. I have already
created
the array formulas to count one text string in a range (i.e. number of
males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

Stupid me, if that works why not use

=COUNTIF(B2:B100,"White Male")

if you are using more than one column (the proper way to do it)


=SUMPRODUCT(--(A2:A100="White"),--(B2:B100=:Male"))


--
Regards,

Peo Sjoblom





"Peo Sjoblom" wrote in message
...
So how are these setup? Are you using 2 columns or do you mean a single
cell can hold "White Male" (not a very good design to have it like that,
next time if possible use multiple cells)


=SUMPRODUCT(LEN(B2:B100)-LEN(SUBSTITUTE(LOWER(B2:B100),"white
male","")))/LEN("White Male")

might work



--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
This formula is calculating the number of times white and male appears in
the
range. However, I need to know how many times white and male appears
together in the range. Ex. the number of males who are also white.
How do I modify the formula to calculate this?

"Peo Sjoblom" wrote:


=SUM(COUNTIF(A2:A100,{"string1","string2"}))



--
Regards,

Peo Sjoblom



"Kholm" wrote in message
...
How do I count the number of occurrences of two text strings in a
range?
I
am compiling demographic information for my boss at work and need to
know
the
number of caucasion males, caucasion females, etc. I have already
created
the array formulas to count one text string in a range (i.e. number of
males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

=SUMPRODUCT(--(B2:B100="Male"),--(C2:C100="White"))

should do it


for better usability replace Male and White in the above formula with for
instance H2 and I2
then put the criteria in those cells, that way you don't need to edit the
formula when you change the criteria


=SUMPRODUCT(--(B2:B100=H2),--(C2:C100=I2))




--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
I think so. Here is what my worksheet looks like.

Applicant Name Sex Race

John Doe Male White
Jane Doe Female Hispanic
James Doe Male African American

I need to be able to calculate how many people are both male and white.
In
this example the answer is 1, but I don't know how to write the formula to
do
this.

When I use 2 formulas and add them like you suggested it gives me an
answer
of 3 as it is counting the number of times Male appears and the number of
times White appears.


I appreciate all your suggestions thus far and will greatly appreciate any
more you may have. I'm on a deadline to get this done for my boss and I'm
stuck.

Thanks.
"Peo Sjoblom" wrote:

So are these part of other strings?

Also note that SUBSTITUTE is case sensitive so it won't count male (you
can
wrap the cell references in LOWER and use "male" and all version will be
counted

Otherwise why don't you just use 2 formulas and add them

=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")+
SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Female","")))/LEN("Female")

also if you replace SUM with SUMPRODUCT you don't need to array enter the
formula


Of course if there are no other strings and your data looks like


Male
Male
Female
etc

you can simply use



=SUM(COUNTIF(A2:A100,{"Male","Female"}))

will do the job if there are no other strings in the cells



--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
I'm not very knowledgable with Excel so I'm not sure how my data is
formatted.

The info I am trying to count is in 2 separate columns. One column is
labeled sex and the other is race.

Here is the array formula I am using to count single strings of text.

=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")

"bj" wrote:

I think we need more info
how is your data formatted?
is the info you are looking for the only thing in a given cell or is
it
only
part of the cell?
What array formula are you currently using?

"Kholm" wrote:

How do I count the number of occurrences of two text strings in a
range? I
am compiling demographic information for my boss at work and need to
know the
number of caucasion males, caucasion females, etc. I have already
created
the array formulas to count one text string in a range (i.e. number
of
males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

Yuck!

=SUMPRODUCT(--(A2:A100="White"),--(B2:B100="Male"))




peo

"Peo Sjoblom" wrote in message
...
Stupid me, if that works why not use

=COUNTIF(B2:B100,"White Male")

if you are using more than one column (the proper way to do it)


=SUMPRODUCT(--(A2:A100="White"),--(B2:B100=:Male"))


--
Regards,

Peo Sjoblom





"Peo Sjoblom" wrote in message
...
So how are these setup? Are you using 2 columns or do you mean a single
cell can hold "White Male" (not a very good design to have it like that,
next time if possible use multiple cells)


=SUMPRODUCT(LEN(B2:B100)-LEN(SUBSTITUTE(LOWER(B2:B100),"white
male","")))/LEN("White Male")

might work



--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
This formula is calculating the number of times white and male appears
in the
range. However, I need to know how many times white and male appears
together in the range. Ex. the number of males who are also white.
How do I modify the formula to calculate this?

"Peo Sjoblom" wrote:


=SUM(COUNTIF(A2:A100,{"string1","string2"}))



--
Regards,

Peo Sjoblom



"Kholm" wrote in message
...
How do I count the number of occurrences of two text strings in a
range?
I
am compiling demographic information for my boss at work and need to
know
the
number of caucasion males, caucasion females, etc. I have already
created
the array formulas to count one text string in a range (i.e. number
of
males
and females), but I do not know how to count two. Please help!

Counting Text Strings
 

Thanks a million. That formula worked.
You're awesome :-)



"Peo Sjoblom" wrote:


=SUMPRODUCT(--(B2:B100="Male"),--(C2:C100="White"))

should do it


for better usability replace Male and White in the above formula with for
instance H2 and I2
then put the criteria in those cells, that way you don't need to edit the
formula when you change the criteria


=SUMPRODUCT(--(B2:B100=H2),--(C2:C100=I2))




--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
I think so. Here is what my worksheet looks like.

Applicant Name Sex Race

John Doe Male White
Jane Doe Female Hispanic
James Doe Male African American

I need to be able to calculate how many people are both male and white.
In
this example the answer is 1, but I don't know how to write the formula to
do
this.

When I use 2 formulas and add them like you suggested it gives me an
answer
of 3 as it is counting the number of times Male appears and the number of
times White appears.


I appreciate all your suggestions thus far and will greatly appreciate any
more you may have. I'm on a deadline to get this done for my boss and I'm
stuck.

Thanks.
"Peo Sjoblom" wrote:

So are these part of other strings?

Also note that SUBSTITUTE is case sensitive so it won't count male (you
can
wrap the cell references in LOWER and use "male" and all version will be
counted

Otherwise why don't you just use 2 formulas and add them

=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")+
SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Female","")))/LEN("Female")

also if you replace SUM with SUMPRODUCT you don't need to array enter the
formula


Of course if there are no other strings and your data looks like


Male
Male
Female
etc

you can simply use



=SUM(COUNTIF(A2:A100,{"Male","Female"}))

will do the job if there are no other strings in the cells



--
Regards,

Peo Sjoblom






"Kholm" wrote in message
...
I'm not very knowledgable with Excel so I'm not sure how my data is
formatted.

The info I am trying to count is in 2 separate columns. One column is
labeled sex and the other is race.

Here is the array formula I am using to count single strings of text.

=SUM(LEN(B2:B100)-LEN(SUBSTITUTE(B2:B100,"Male","")))/LEN("Male")

"bj" wrote:

I think we need more info
how is your data formatted?
is the info you are looking for the only thing in a given cell or is
it
only
part of the cell?
What array formula are you currently using?

"Kholm" wrote:

How do I count the number of occurrences of two text strings in a
range? I
am compiling demographic information for my boss at work and need to
know the
number of caucasion males, caucasion females, etc. I have already
created
the array formulas to count one text string in a range (i.e. number
of
males
and females), but I do not know how to count two. Please help!