Results 1 to 6 of 6
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count multiple IF conditions (Excel 2003)

    I'm lost again trying to apply multiple conditions for COUNTIF. I don't think COUNTIF works with multiple conditions but I can't figure out what else to use. I have a table of names and numbers assigned. Each name can have numbers 0-7 but these 8 numbers are grouped into 4 categories like this:
    <pre>RULES: CategoryA = 0, CategoryB=1, 2, CategoryC=3, 4, 5 and CategoryD = 6, 7
    dog : 4 : 5
    mouse: 1 : 5
    horse: 0 : 3</pre>

    <pre>The count of each animal per category is :
    CategoryA CategoryB CategoryC CategoryD
    dog 0 0 2 0
    mouse 0 1 1 0
    horse 1 0 1 0</pre>

    So what I need is the total count (not sum) of each occurence in its category (which has multiple members). I was trying to do something like COUNTIF(animal_range,"=1" and "=2") to count the number of times "1" and "2" appeared, for example but of course I can't use more than one criteria with COUNTIF. I assume this needs an array formula but I'm pathetic at getting those to work.

    The real workbook needs a summary which totals the number of companies that fall into these categories and each category has a set of sub-categories but the summary tracks the major categories not the sub-categories.

    Thnx, Deb

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

    Re: count multiple IF conditions (Excel 2003)

    Wouldn't a simple addition work?

    =COUNTIF(Range,"1")+COUNTIF(Range,"2")+......
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count multiple IF conditions (Excel 2003)

    Yes that was my first thought, just add up the conditons like AND statements in an SQL statement but for some reason it's counting way more than is there. I attached a stripped down version removing all confidential stuff but showing the two cols that I care about (with random data). This should be easy and I can't see the problem.

    // Thnx, Deb
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: count multiple IF conditions (Excel 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 14-Dec-07 13:04. Added PS)</P>In G5:
    =SUMPRODUCT(($D$12:$D$40=$E5)*($F$12:$F$40>=1)*($F $12:$F$40<=2))

    In H5:
    =SUMPRODUCT(($D$12:$D$40=$E5)*($F$12:$F$40>=3)*($F $12:$F$40<=5))

    In I5:
    =SUMPRODUCT(($D$12:$D$40=$E5)*($F$12:$F$40>=6)*($F $12:$F$40<=7))

    Copy G5:I5 to G6:I9

    Steve
    PS if you want to be explicit about =1 and =2, etc instead of the ranges, you can use:
    G5
    =SUMPRODUCT(($D$12:$D$40=$E5)*(($F$12:$F$40=1)+($F $12:$F$40=2)))

    H5
    =SUMPRODUCT(($D$12:$D$40=$E5)*(($F$12:$F$40=3)+($F $12:$F$40=4)+($F$12:$F$40=5)))

    I5
    =SUMPRODUCT(($D$12:$D$40=$E5)*(($F$12:$F$40=6)+($F $12:$F$40=7)))

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count multiple IF conditions (Excel 2003)

    Yes this is exactly <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> what I needed. I had tried SUMPRODUCT but wasn't combining it correctly to do the ANDing function. I'll definitely be using this a lot for other problems.

    Thanks much, Deb

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: count multiple IF conditions (Excel 2003)

    Just to be explict:
    To do ANDs with the SUMPRODUCT (and other array techniques) one multiplies (ie uses an asterisk[*])

    To do ORs with the SUMPRODUCT (and other array techniques) one adds (ie uses a plus [+])

    MS MVP Chip Pearson has a very nice Array Formula Primer and MS MVP Bob Umlas has a nice article on Array Formulas as well.

    Steve

Posting Permissions

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