Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif for specific cells rather than a range ???? | Excel Worksheet Functions | |||
Paste to adjacent cells | Excel Discussion (Misc queries) | |||
selecting non adjacent cells | Excel Worksheet Functions | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
CountIF cells are not empty | Excel Discussion (Misc queries) |