# Thread: COUNTIF using two arguments (Excel2000)

1. ## COUNTIF using two arguments (Excel2000)

I'm analysing school report data. I have four columns Col A "Name", Col B "Gender", Col C "Non-English", Col D "Test Score". I've used COUNTIF to count the number of students with certain scores and now want to break that down into gender groups and those from non-English speaking background.
Formula needs to be something like:- If cell in Col B = male and cell in Col C = non-English, then count scores <10. I know its simple but I can't get it right. Looking foward to a simple solution.

2. ## Re: COUNTIF using two arguments (Excel2000)

You might want to check out the Pivot Table method in Excel (Data, pivot table and Pivotchart report).

Otherwise you could use a formula like this:

=SUMPRODUCT(B2:B100="Male")*(C2:C100="English")*(D 2100<10))

3. ## Re: COUNTIF using two arguments (Excel2000)

Thanks for the quick response, pieterse. I can't get the formula to work. I've attached the sample spreadsheet to give you a look.
Cheers - Nuggett

4. ## Re: COUNTIF using two arguments (Excel2000)

There were some brackets missing:

=SUMPRODUCT((\$C\$2:\$C\$13="M")*(\$D\$2:\$D\$13="Y")*(\$E\$ 2:\$E\$13<10))
=SUMPRODUCT((\$C\$2:\$C\$13="F")*(\$D\$2:\$D\$13="Y")*(\$E\$ 2:\$E\$13<10))
=SUMPRODUCT((\$C\$2:\$C\$13="M")*(\$D\$2:\$D\$13<>"Y")*(\$E \$2:\$E\$13<10))
=SUMPRODUCT((\$C\$2:\$C\$13="F")*(\$D\$2:\$D\$13<>"Y")*(\$E \$2:\$E\$13<10))

5. ## Re: COUNTIF using two arguments (Excel2000)

Jan Karel, I fell for the old nesting trick. Thanks for <img src=/S/bash.gif border=0 alt=bash width=35 height=39> banging this on the head so quickly. I kept trying IF and COUNTIF and AND statements but your solution makes sense and is so simple THANKS again.
Cheers - Nuggett

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•