Counting Text Strings

[Kholm]

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!

 

[Peo Sjoblom]

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

 

[bj]

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]

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

 

[Kholm]

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]

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

 

[Peo Sjoblom]

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]

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]

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]

=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

 

[Peo Sjoblom]

Yuck!

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




peo

 

[Kholm]

Thanks a million. That formula worked.
You're awesome