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)
>

"Peo Sjoblom" <PeoSjoblom@discussions.microsoft.com> 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.

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)
>

"Peo Sjoblom" <PeoSjoblom@discussions.microsoft.com> 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.

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)

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)
>

"Peo Sjoblom" <PeoSjoblom@discussions.microsoft.com> 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.