Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: counting problem

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a series of numbers in cell B1 thru B26. Is there a way to count the numbers in this range that are >1 and <11 and have this number show up in cell B27?
    The count if function will only work with one criteria.
    Any suggestions?

    Thanks in advance.

    Jerry

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-15 17:43, jrussell wrote:
    I have a series of numbers in cell B1 thru B26. Is there a way to count the numbers in this range that are >1 and <11 and have this number show up in cell B27?
    The count if function will only work with one criteria.
    Any suggestions?

    Thanks in advance.

    Jerry
    in B27 put

    =COUNTIF(B1:B26,"<11")-COUNTIF(B1:B26,"<=1")

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I applied your suggestion and modified it a bit.
    The following works great.

    Thanks for the help.

    =COUNTIF(B1:B26,">0")-COUNTIF(B1:B26,">11")

    Jerry

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    =SUM(IF(A1:A26<11,IF(A1:A26>1,1,0),0))

    This is an array formula and must be enter be pressing Ctrl-Alt-Enter together. When enter correctly Excel will put {} around the formula to identify it as an array.

    It's never too late to learn something new.

    Ricky

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-15 18:15, Ricky Morris wrote:
    Try this:

    =SUM(IF(A1:A26<11,IF(A1:A26>1,1,0),0))

    This is an array formula and must be enter be pressing Ctrl-Alt-Enter together. When enter correctly Excel will put {} around the formula to identify it as an array.

    i think you mean ctrl+shift+enter?

    [ This Message was edited by: anno on 2002-04-15 19:09 ]

    [ This Message was edited by: anno on 2002-04-15 19:12 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In the reply from Yogi Anand,
    Instead of choosing the range <11, <=1 , suppose I have 100 (say) in a cell,, can I refer the conditions to this cell? e.g

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In the reply from Yogi Anand,
    Instead of choosing the range <11, <=1 , suppose I have 100 (say) in a cell,, can I refer the conditions to this cell? e.g
    Thanks for your help

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chaju:
    I don't see why not! ... You see what I did was first counted all the cells that were less than 11 (this would cover if a cell were valued at 100; then from this count, I deducted the count of cells that were valued at 1 or less (meaning 0)
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi! I understand the formula is ok.. but however i have 100 in cell E1 and change the formula to the bottom shown:

    COUNTIF($C$4:$M$35,"
    The result shows up 0, which is incorrect!
    I want to count withing range -1000 Pls advise, thanks!

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-15 19:52, chaju wrote:
    Hi! I understand the formula is ok.. but however i have 100 in cell E1 and change the formula to the bottom shown:

    COUNTIF($C$4:$M$35,"
    The result shows up 0, which is incorrect!
    I want to count withing range -1000 Pls advise, thanks!
    Please post part of your list and your counting criteria ... nd then let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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
  •