+ Reply to Thread
Results 1 to 3 of 3

How do I use countif across non-adjacent cells?

  1. #1
    Nick
    Guest

    How do I use countif across non-adjacent cells?

    I'm trying to use the Countif (or other) function across non-adjacent cells
    in a long column in order to count the number of occurances of specific cells
    that exceed a limit. The application is the 31 days of the month with 24
    hours following each day. The hourly rows are grouped (normally hidden) and
    do not directly relate to the daily information being counted. I just want to
    count the daily occurances of a limit being exceeded. Something like:
    Countif((B1 or B25 or or B50 ...31st day), ">3.3")
    Data example
    1/1/05 3.0
    cells with hours (skip next 24)
    1/2/05 3.4
    cells with hours (skip next 24)


  2. #2
    Peo Sjoblom
    Guest

    RE: How do I use countif across non-adjacent cells?

    If there is a pattern like every 25th (that would be B1, B26, B51 etc) cell
    you can use

    =SUMPRODUCT(--(MOD(ROW(B1:B2500),25)=1),--(B1:B2500>3.3))

    Otherwise you can use something similar to this link

    http://tinyurl.com/7ntmj

    it is hardly worth it unless there are a lot od non contigious cells and
    only then, the formula can be made to look better if you name the non
    contigious cells and use the name


    Regards,

    Peo Sjoblom


    "Nick" wrote:

    > I'm trying to use the Countif (or other) function across non-adjacent cells
    > in a long column in order to count the number of occurances of specific cells
    > that exceed a limit. The application is the 31 days of the month with 24
    > hours following each day. The hourly rows are grouped (normally hidden) and
    > do not directly relate to the daily information being counted. I just want to
    > count the daily occurances of a limit being exceeded. Something like:
    > Countif((B1 or B25 or or B50 ...31st day), ">3.3")
    > Data example
    > 1/1/05 3.0
    > cells with hours (skip next 24)
    > 1/2/05 3.4
    > cells with hours (skip next 24)
    >


  3. #3
    Harlan Grove
    Guest

    Re: How do I use countif across non-adjacent cells?

    "Peo Sjoblom" <[email protected]> wrote...
    ....
    >Otherwise you can use something similar to this link
    >
    >http://tinyurl.com/7ntmj
    >

    ....

    Which uses LARGE((rng),ROW(INDIRECT("1:"&sizeofrng))) to pull all numeric
    values from the arbitrary range rng. But it requires less processing time to
    use INDEX(FREQUENCY((rng),{5}),2) to count all numbers in rng greater than
    5.

    For counting, FREQUENCY is usually best. For summing, LARGE or SMALL may
    work better.



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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