Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Nick
 
Posts: n/a
Default 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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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:B25003.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   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Peo Sjoblom" 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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif for specific cells rather than a range ???? Renee - California Excel Worksheet Functions 5 May 27th 05 07:09 PM
Paste to adjacent cells Dave Excel Discussion (Misc queries) 3 May 25th 05 02:06 AM
selecting non adjacent cells fran Excel Worksheet Functions 4 April 25th 05 12:54 AM
counting cells (COUNTIF) based on two or more criteria Tricia S. Excel Worksheet Functions 10 March 17th 05 03:17 PM
CountIF cells are not empty Wayne Excel Discussion (Misc queries) 3 January 6th 05 05:44 PM


All times are GMT +1. The time now is 02:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"