How to look through non-contiguous cell arrays using COUNTIF

[djk957]

I want to create a formula that will look through several cell arrays to determine if a cell (one or more) have a value greater than 0. If so, the return a true condition.

Here is the formula I have tried

=IF(COUNTIF(TST_RANGE,">0"),1,0) (doesn't work)

TST_RANGE is a named cell range and is defined as
=Chart1!$L$5:$L$9,Chart1!$N$5:$N$9

The formula is operating on cell arrays that are not contiguous.

The formula works OK if TST_RANGE is defined as with contiguous columns.

My data is in non-contiguous columns so I need to scan the first array, skip several columns, scan the next array, skip columns, so on and so forth.

 

[Kenneth Hobson]

Enclose your DisRange in ()'s.

Function DisRangeCountIf(ByVal DisRange As Range, ByVal sCriteria As String) As Long
  Dim lngCount As Long
  dblCount = 0
  For Each cell In DisRange.Cells
    lngCount = lngCount + WorksheetFunction.CountIf(cell, sCriteria)
  Next
  DisRangeCountIf = lngCount
End Function

Function DisRangeSumIf(ByVal DisRange As Range, ByVal sCriteria As String) As Double
  Dim dblSum As Long
  dblSum = 0
  For Each cell In DisRange.Cells
    dblSum = dblSum + WorksheetFunction.SumIf(cell, sCriteria)
  Next
  DisRangeSumIf = lngCount
End Function

 

[wigi]

That's too bad.

You might find the INDEX(FREQUENCY formula is a very useful and efficient formula.

For example, suppose you want to get a count of numbers >0 in the same range across 10 sheets.

=INDEX(FREQUENCY(Sheet1:Sheet10!A1:B10,0),2)

Plenty of benefit if you ask me!

Nice work, but it remains that for this topic I think it is overkill.

It's interesting for other questions and topics, but then I think of it as a theoretical exercise (as written earlier). It could be very handy but not in this topic.

Best regards,

Wigi

 

[T. Valko]

Nice work, but it remains that for this topic I think it is overkill.

It's interesting for other questions and topics, but then I think of it as a theoretical exercise (as written earlier). It could be very handy but not in this topic.

Best regards,

Wigi

It's totally appropriate for this topic and is not overkill.

 

[markmzz]

Another formula:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Col01</td><td style="text-align: center;;">Col02</td><td style="text-align: center;;">Col03</td><td style="text-align: center;;"></td><td style="text-align: center;;">Exist >0</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=1*(<font color="Blue">SMALL(<font color="Red">TST_RANGE,COUNT(<font color="Green">TST_RANGE</font>)</font>)>0</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">TST_RANGE</th><td style="text-align:left">=Sheet4!$A$2:$A$6,Sheet4!$C$2:$C$6</td></tr></tbody></table></td></tr></table><br />
Markmzz