Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countif depending on three condictions (excel 2000)

    Hi all
    How can I count the items depending on the contents in three columns.
    just something like this
    =countif(a:a,"=apple" and (b:b,">1") and (c:c,this cell is empty))
    how can I do this function correctly?
    thank you

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif depending on three condictions (excel 2000)

    COUNTIF only works with a single condition. You will need an array formula to do this. Something like this:

    <pre>=SUM((A1:A100="apple")*(B1:B100>1)*(C1:C100=" "))
    </pre>


    You must hold down the Shift and Ctrl keys when you hit Enter to make that an array formula.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif depending on three condictions (excel 2000)

    Hi Legare
    that is perfect for my case, thank you, but
    1) that does not work with excel 97(I have other older computer)
    2) if I change the condition to or (count items meet any one of the condition)
    any help will be appreciated.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif depending on three condictions (excel 2000)

    The array-formula Legare suggested should work in Excel 97: It needs to be array-entered -- that is, as described to you, you need to hit control+shift+enter at the same time, not just enter.

    What follows is an equivalent formula that can be entered normally:

    =SUMPRODUCT((A1:A100="apple")*(B1:B100>1)*(LEN(C1: C100)=0))
    Microsoft MVP - Excel

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif depending on three condictions (excel 2000)

    I don't know of any reason why that formula would not work on Excel 97. I use to use formula like that in 97 all the time. However, I don't have Excel 97 available so I can't try it.

    The formula below should give you the or condition:

    <pre>=SUM((((A1:A100="apple")+(B1:B100>1)+(C1:C100 =""))>0)*1)
    </pre>

    Legare Coleman

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

    Re: countif depending on three condictions (excel 2000)

    Those formula's also work in XL5/95.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif depending on three condictions (excel 2000)

    Are you sure that you entered it as an array formula: Enter<pre>=SUM((A1:A100="apple")*(B1:B100>1)*(C1:C 100=""))</pre>

    in the formula bar. Then press the <Ctrl> <Shift> and <Enter> keys at the same time. If you do this, Excel adds braces around the formula and you should get the correct results. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif depending on three condictions (excel 2000)

    I found out why that is not working in excel 97 because my data has head row(a1is item, b1 is qty, c1is delivery date), and I need to change the range from a1:a100 to a2:a100, b1:b100 to b2:b100, c1:c100 to c2:c100. still do not understand why I need to do so.
    thank you all

Posting Permissions

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