Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countif noncontiguous (Excel 2007)

    Is it possible to have the COUNTIF function operate on a set of noncontiguous cells?

    For example, I'd like to create a named range of cells (Named). The cells in the named range are noncontiguous (e.g. B5, C7, D2).

    Then I'd like to create a formula that is "=COUNTIF(Named,"test")" so that it will count the number of cells in my named range that contain the word test.

    Thanks...

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

    Re: countif noncontiguous (Excel 2007)

    COUNTIF doesn't work with non-contiguous ranges. There may be complicated array formula solutions, or you could create a custom function:

    Function MyCountIf(oRange, aValue) As Long
    Dim oCell As Range
    For Each oCell In oRange.Cells
    MyCountIf = MyCountIf - (oCell = aValue)
    Next oCell
    End Function

    and use it like this:

    =MyCountIf(Named, "Test")

    If you store the function in your personal macro workbook Personal.xlsm, use

    =Personal.xlsm!MyCountIf(Named, "Test")

  3. #3
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif noncontiguous (Excel 2007)

    You are simply amazing

    Thanks

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif noncontiguous (Excel 2007)

    Hans,

    I know your code works because I tried it, but I'm puzzled:
    When oCell=aValue, ie when "Test"="Test" my logic tells me this is True which seems to me SHOULD return a 1 rather than a negative 1.
    Hence, I would have (without testing) instinctively written
    MyCountif = MyCountif + (oCell = aValue)

    Can you explain the error in my thinking?

    Thanks

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

    Re: countif noncontiguous (Excel 2007)

    In Visual Basic, True and False are stored as integers, where True = -1 and False = 0.

    In a computer, the number -1 is stored as a series of bits that are all "on", and the number 0 as a series of bits that are all "off", hence the use of -1 for True and 0 for False.

    (In theory, it would be more efficient to store True and False as single bits, where True = 1 and False = 0. However, the CPUs used in personal computers are optimized for handling data in chunks of 8, 16, 32 and 64 bits, handling single bits is relatively slow. Hence the decision to store True and False as integers)

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif noncontiguous (Excel 2007)

    COUNTIF can work with non-contiguous ranges, and it's not even array entered !

    =SUM(COUNTIF(INDIRECT({"B5","C7","D2"}),"test"))

    Regards
    Bosco

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

    Re: countif noncontiguous (Excel 2007)

    That's very clever <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> but does it work with a defined named that refers to a non-contiguous range?

Posting Permissions

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