Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Kalgoorlie, Western Australia, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Location
    Kalgoorlie, Western Australia, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Location
    Kalgoorlie, Western Australia, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •