+ Reply to Thread
Results 1 to 7 of 7

How does AND work in array formulas?

  1. #1
    Registered User
    Join Date
    04-13-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    26

    How does AND work in array formulas?

    How does AND work in array formulas?

    I want to count the number of positive numbers in a named range that meet certain criteria, but my formula isn’t working as I expect. In the formulas below, the named range “SaleAmount” contains one text value, 56 positive numbers, and several hundred empty cells.

    This array formula returns the value 56, which is correct:
    {=COUNT(IF(SaleAmount>0,SaleAmount,FALSE))}

    I think the following array formula should also return 56, but the value returned is 1.
    {=COUNT(IF(AND(SaleAmount>0),SaleAmount,FALSE))}
    Why does this array formula return the value 1 instead of 56?

    This array formula should return the value 20, but also returns the value of 1.
    =COUNT(IF(AND(SaleAmount>0,SaleAmount<100000),SaleAmount,FALSE))
    Why does this array formula return the value 1 instead of 20 ?

    Can anyone suggest what I am doing wrong?
    Thanks in advance for any explanation.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: How does AND work in array formulas?

    If you have a formula that works, why are you trying to do something that is wrong?

    You don't need to use AND in such an array formula. If you want to see why it's not working, select the cell with the formula and select Evaluate Formula to step through its calculation process. You'll see why it's not working.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How does AND work in array formulas?

    AND only works with single cells, so it cannot be used in an array formula like that. If you want to set up an AND condition in an array formula, then you use the * operator, and for COUNT I prefer to just add 1's, like this:

    =SUM(IF((SaleAmount>0)*(SaleAmount<100000),1,0))

    Commit with Ctrl-Shift-Enter.

    Hope this helps.

    Pete

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How does AND work in array formulas?

    @Pete_UK, given the explicit coercion of the Boolean Arrays the IF is arguably superfluous

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How does AND work in array formulas?

    That's true, but I use the SUM(IF... for both summing and counting - for summing you just change the 1 to the sum range, so it's fairly easy to remember for new users.

    Actually, the zero is also superfluous in my formula, but I've put it in as most new users can relate to it more easily.

    Pete

  6. #6
    Registered User
    Join Date
    04-13-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: How does AND work in array formulas?

    Thanks to all who responded. I think the SUM(IF....) suggested by Pete_UK is what I want. I also want to do AVERAGE and MEDIAN on the same range, as well as add more conditional selection criteria. Thanks also for the tip about the "Evaluate Formula". I finally found it under Tools>Formula Auditing. Also, what is the "explicit coercion of Boolean arrays" mentioned by Donkeyote?

    Thanks again - I will be working on these formulas during the next week.

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How does AND work in array formulas?

    wont just =COUNTIF(SaleAmount,">"&0) work?

    =COUNTIF(SaleAmount,">"&0)-COUNTIF(SaleAmount,">="&10000)
    Last edited by martindwilson; 04-28-2012 at 07:35 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1