Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Using "CountA" function with a Range

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello. I would like to get a count of all items in a column which fall between a range. For instance, how many items cost more than $5 and less than $10. Can anyone help me out? Many thanks.


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    for A1:A10, you could use
    =COUNTIF(A1:A10,">"&5)-COUNTIF(A1:A10,">="&10)

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-03 07:32, IML wrote:
    for A1:A10, you could use
    =COUNTIF(A1:A10,">"&5)-COUNTIF(A1:A10,">="&10)
    Nice one IML,

    quick query : ">5" seems to work in place of ">"&5

    (although not in your second arguement, which is correct)

    do you default to this just out of habit, or is there a subtelty I'm missing ?

    thanks
    Chris

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-03 12:45, Chris Davison wrote:
    On 2002-05-03 07:32, IML wrote:
    for A1:A10, you could use
    =COUNTIF(A1:A10,">"&5)-COUNTIF(A1:A10,">="&10)
    Nice one IML,



    Chris

    (although not in your second arguement, which is correct)

    do you default to this just out of habit, or is there a subtelty I'm missing ?

    thanks


    Thanks Chris,
    I think

    =COUNTIF(A1:A10,">5")-COUNTIF(A1:A10,">=10")

    would be considered more correct. The amperstand is a bad habit since I'm use to tying it to cells, as opposed to constants.
    quick query : ">5" seems to work in place of ">"&5

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sounds like my "indirects" where everything is inside quotes until the variables....

    your "habit" still gives the same answer, I should add, just in case the reader thinks it's a no-no


  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-03 13:04, Chris Davison wrote:
    sounds like my "indirects" where everything is inside quotes until the variables....

    your "habit" still gives the same answer, I should add, just in case the reader thinks it's a no-no

    The use of & in COUNTIF and SUMIF helps to distinguish between constants and variables. Ian's "habit", that is, ...&a-constant has a good side-effect:

    When you have the following in a French or Dutch version of Excel (Excel 97, anyway):

    =COUNTIF(A1:A10,">10,5")

    don't try to send your WB to an American with the US version of Excel. However,

    =COUNTIF(A1:A10,">"&10,5)

    will give the American no problem.



    Aladin

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    interesting !

    Any insight as to why Dutch or French syntaxes are different ?

    rephrase : are both syntaxes acceptable to Excel, just that Netherlands and France prefer option (a) for some reason

    or is Excel actually coded differently to take account of preferences in syntax by Netherland and France ?


    .... tangent : is this just Excel ? Or is it Dutch and French computing in general ? What happens when France wants to launch a rocket to the International Space Station ? is there lots of "computer translations" ?

    sorry...silly questions
    interesting topic though




  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-03 15:35, Chris Davison wrote:
    Aladin,

    interesting !

    Any insight as to why Dutch or French syntaxes are different ?

    rephrase : are both syntaxes acceptable to Excel, just that Netherlands and France prefer option (a) for some reason

    or is Excel actually coded differently to take account of preferences in syntax by Netherland and France ?


    .... tangent : is this just Excel ? Or is it Dutch and French computing in general ? What happens when France wants to launch a rocket to the International Space Station ? is there lots of "computer translations" ?

    sorry...silly questions
    interesting topic though

    [img]/board/images/smiles/icon_smile.gif[/img]

    I think it's just a bug in Excel's parser itself (Excel 97).

    A Dutch WB can be immediately opened with the American version. All function names will change immediately to their English version. But, this process does not seem to look at antthing between double quotes. So you get from:

    =AANTAL.ARG(A1:A10,">1,6")

    =COUNTIF(A1:A10,">1,6") instead of:

    =COUNTIF(A1:A10,">1.6")

    I've seen this happen at school (all English) with files set up in the Dutch version.

    Can anyone verify this?

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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