Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNTIF with AND condition (2003)

    I am trying to do a countif to find a range where the value falls between certain amounts. Example, range 'fee2' where the value is greater than $1M and less than $2M. So far my formula has failed: =COUNTIF(fee2,AND(">=1000000","<2000000")). Does anyone know of a way to do this? Thank you very much, Norma

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF with AND condition (2003)

    This is done using a SUMPRODUCT formula or an array formula with SUM:

    Possibility 1:

    =SUMPRODUCT((fee2>=1000000)*(fee2<2000000))

    Possibility 2 - array formula (confirm with Ctrl+Shift+Enter):

    =SUM((fee2>=1000000)*(fee2<2000000))

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF with AND condition (2003)

    Thank you SO MUCH! It's brilliant, Norma

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: COUNTIF with AND condition (2003)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    I am also looking for a solution to count values within a selected range that meet certain criteria, so I thought =COUNTIF(G10:G75,AND(">0","<=249.99")) looked like a solution. Apparently not. That's why I turn to Woody's Lounge.
    I have found an article at Tech Republic (can I say that here?) http://articles.techrepublic.com.com/5100-...11-6073927.html that explains the use of a custom function.

    In a VB module in the workbook:

    Function COUNTBETWEEN(rng, num1, num2)
    COUNTBETWEEN = Application.CountIf(rng, "<=" & num2) - Application.CountIf(rng, "<" & num1)
    End Function

    The cell entry is: =COUNTBETWEEN(H1:H400,10,20)

    But I can only get the results to appear once, at the moment I press enter, having just typed in the formula. The article does not explain how to refresh and F9 and Alt+F9 don't seem to force a recalc.

    Can you offer a solution to this solution?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF with AND condition (2003)

    If calculation is set to automatic in the Calculate tab of Tools | Options..., the result of the formula =COUNTBETWEEN(H1:H400,10,20) should update itself automatically as you change values in the range H1:H400. But make sure that the formula is not in a cell in the range H1:H400 - that would lead to a circular reference.

    Oh, and there is no objection to mentioning other discussion sites such as TechRepublic! We all get our information where we can. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: COUNTIF with AND condition (2003)

    Thanks Hans. COUNTBETWEEN now works great. (I flubbed my criteria.)

    But the first two Possibilities you offered, SUMPRODUCT and SUM array work fine too. And they don't require opening a module.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF with AND condition (2003)

    In general, solutions involving only built-in functions are to be preferred, since they operate much faster than VBA code. But if the formulas become so complex that they become unwieldy, or what you want cannot be accomplished using built-in functions, a custom VBA function is a good alternative.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: COUNTIF with AND condition (2003)

    Since the COUNTBETWEEN UDF simply uses the COUNTIF worksheet function, I would just use that in the worksheet directly rather than having to worry about macro warnings and users disabling macros. But obviously there are times when a UDF is the best way to go.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: COUNTIF with AND condition (2003)

    Hi Arcturus16a,

    You could also use:
    =COUNTIF(G10:G75,">0)-COUNTIF(G10:G75,">249.99")
    Not being an array formula (which SUMPRODUCT is even though you don't enter it as one), it should calculate faster. Neither does it rely on a UDF.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: COUNTIF with AND condition (2003)

    Hi nthayer,

    Regarding your original formula, you can make it work with:
    =COUNTIF(fee2,"<2000000")-COUNTIF(fee2,"<1000000")
    Not being an array formula (and this includes SUMPRODUCT) it should calculate faster.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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