CountIf Function

[kinseyj]

Hi I have a spreadsheet that contains name, gender, race, and supervisory level for all employees. I'm trying to create a formula that will allow me to count the number of--- for instance --Hispanic females in a supervisory position. which will be combining three columns.

Column C - Gender
Column D - Race
Comun G - Level (supervisor, manager, other)

How can I combine this countif formula. This is the formula I'm using:

=COUNTIF(C2:C5,"Female")+COUNTIF(D2:D5,"Black")+COUNTIF(G2:G5,"O")

But instead of bringing back the number "1" which is what it should be.. it's bring back "7" because it is counting the total number of times it sees the word female, Black & Other fields' within the range I have selected. Am I using the wrong formula?

Any help will be greatly appreciated. This report is due MONDAY ...

Jacentia

 

[Matty]

Hi,

Try:

=SUMPRODUCT(--(C2:C5="Female"),--(D2:D5="Black"),--(G2:G5="O"))

Matty

 

[kinseyj]

No, it's giving me true or false instead of a count.

 

[T. Valko]

Hi I have a spreadsheet that contains name, gender, race, and supervisory level for all employees. I'm trying to create a formula that will allow me to count the number of--- for instance --Hispanic females in a supervisory position. which will be combining three columns.

Column C - Gender
Column D - Race
Comun G - Level (supervisor, manager, other)

How can I combine this countif formula. This is the formula I'm using:

=COUNTIF(C2:C5,"Female")+COUNTIF(D2:D5,"Black")+COUNTIF(G2:G5,"O")

But instead of bringing back the number "1" which is what it should be.. it's bring back "7" because it is counting the total number of times it sees the word female, Black & Other fields' within the range I have selected. Am I using the wrong formula?

Any help will be greatly appreciated. This report is due MONDAY ...

Jacentia

If you're using Excel 2007 or later...

Use cells to hold the criteria:

• A1 = Female
• B1 = Black
• C1 = O

Then:

=COUNTIFS(C2:C5,A1,D2:D5,B1,G2:G5,C1)

 

[Matty]

Works OK for me:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:13px;"><col style="width:13px;"><col style="width:64px;"><col style="width:27px;"><col style="width:41px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Gender</td><td>Race</td><td>
</td><td>
</td><td>Level</td><td>
</td><td>Count</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Female</td><td>White</td><td>
</td><td>
</td><td>P</td><td>
</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Male</td><td>White</td><td>
</td><td>
</td><td>P</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Female</td><td>Black</td><td>
</td><td>
</td><td>O</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Female</td><td>Black</td><td>
</td><td>
</td><td>P</td><td>
</td><td>
</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>I2</td><td>=SUMPRODUCT(--(C2:C5="Female"),--(D2:D5="Black"),--(G2:G5="O"))</td></tr></tbody></table></td></tr></tbody></table>
What version of Excel are you using?

Matty

 

[kinseyj]

I'm using Excel 2010... Let me try the formula you just posted..

 

[Matty]

I'm using Excel 2010... Let me try the formula you just posted..

Given you're using Excel 2010, Biff's solution's best...

Matty

 

[kinseyj]

AHHH!! MATTY!! THAT'S IT, THE LAST FORMULA YOU GAVE DID IT. Thank you sooooooooo much!!! PERFECT!!

 

[michaelbarry]

I tried out these excel functions in 2007 and they worked for me as well. Trying out the functions given on these forums helps a newbie out like me understand the ins and outs of Excel. This was a basic task that I can actually incorporate with what my new job has required of me now. Our new bosses want everything in a clean, neat, and organized displayed fashion. Lucky for me, I got assigned the task.