• countif depending on three condictions (excel 2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » countif depending on three condictions (excel 2000)

    • This topic has 7 replies, 5 voices, and was last updated 23 years ago.
    Author
    Topic
    WSjoe8888
    AskWoody Lounger
    March 8, 2002 at 6:34 pm #367950

    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

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • H. Legare Coleman
      AskWoody Plus
      March 8, 2002 at 6:54 pm #575218

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

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

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

      Reply | Quote
      • WSjoe8888
        AskWoody Lounger
        March 9, 2002 at 1:57 am #575307

        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.

        Reply | Quote
        • WSAladin Akyurek
          AskWoody Lounger
          March 9, 2002 at 9:15 am #575344

          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))

          Reply | Quote
        • H. Legare Coleman
          AskWoody Plus
          March 9, 2002 at 6:06 pm #575389

          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:

          =SUM((((A1:A100="apple")+(B1:B100>1)+(C1:C100=""))>0)*1)
          Reply | Quote
        • WSSammyB
          AskWoody Lounger
          March 11, 2002 at 1:10 pm #575531

          Are you sure that you entered it as an array formula: Enter

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

          in the formula bar. Then press the and keys at the same time. If you do this, Excel adds braces around the formula and you should get the correct results. –Sam

          Reply | Quote
          • WSjoe8888
            AskWoody Lounger
            March 11, 2002 at 1:26 pm #575535

            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

            Reply | Quote
    Viewing 0 reply threads
    Reply To: countif depending on three condictions (excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.