Finding lowest count in any column

[Sarah H.]

Hi, group,

I'm interested in knowing the lowest count of non-zero numbers in any column
on my sheet.
Columns are from B through I.

Actually, I want to add 1 to the low count - because of the header row.

Using ordinary functions I did it this way:

=MIN(COUNTIF(B:B,">0"),COUNTIF(C:C,">0"),COUNTIF(D,">0"),COUNTIF(E:E,">0"),COUNTIF(F:F,">0"),COUNTIF(G:G,">0"),COUNTIF(H:H,">0"),COUNTIF(I:I,">0"))+1

The columns look like this:

21-Nov-08 43.44% 159.59% 12.32% 242.42% 76.30% 112.34% 27.79%
219.12%
20-Nov-08 42.15% 167.40% 11.57% 260.63% 69.85% 127.98% 24.91%
248.27%
19-Nov-08 47.22% - 14.33% 221.28% 100.00% 100.00% 29.06%
219.12%
18-Nov-08 52.74% - 18.36% 182.31% - - 32.28%
197.03%
17-Nov-08 53.52% - 18.74% 179.29% - - 32.04%
200.40%
14-Nov-08 57.16% - 20.74% 163.84% - - 33.32%
191.11%

(The dashes are zero-values.)

Column C above has 2 non-zero values. It's the lowest columnar count across
the
sheet. That's what I want to know.

So my question is: is there a better, as in more efficient, way to do this?
For example, I could search for the row number of the first zero-value using
MATCH.
I don't know if that would be faster or better, but my instinct says it
would be slower.
But maybe some cross-range way would be better rather than doing each column
at a time.

Thanks for any ideas!
Sarah

 

[Don Guillett]

How about a nice macro
Sub lowestcolumncount()
minnum = 20
mycol = 10
For i = 2 To 10
mc = Application.CountIf(Columns(i), ">0")
'MsgBox mc
If mc < minnum Then
minnum = mc
mycol = i
End If
'MsgBox minnum
Next i
MsgBox "Column " & mycol & " " _
& " Min count=" & minnum
End Sub

 

[Don Guillett]

How about a nice macro
Sub lowestcolumncount()
minnum = 20
mycol = 10
For i = 2 To 10
mc = Application.CountIf(Columns(i), ">0")
'MsgBox mc
If mc < minnum Then
minnum = mc
mycol = i
End If
'MsgBox minnum
Next i
MsgBox "Column " & mycol & " " _
& " Min count=" & minnum
End Sub

 

[T. Valko]

Actually, the way you're doing it is pretty good although the formula is
sort of long.

A shorter formula but not necessarily better since it's volatile:

Array entered** :

=MIN(COUNTIF(OFFSET(B2:G10,,COLUMN(B2:G10)-COLUMN(B2),,1),">0"))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Another option and probably the best approach is to use a separate COUNTIF
on each column then get the MIN from that range. For example:

On row 11 starting in B11 and copied across to G11:

=COUNTIF(B2:B10,">0")

Then:

=MIN(B11:G11)+1

 

[T. Valko]

Actually, the way you're doing it is pretty good although the formula is
sort of long.

A shorter formula but not necessarily better since it's volatile:

Array entered** :

=MIN(COUNTIF(OFFSET(B2:G10,,COLUMN(B2:G10)-COLUMN(B2),,1),">0"))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Another option and probably the best approach is to use a separate COUNTIF
on each column then get the MIN from that range. For example:

On row 11 starting in B11 and copied across to G11:

=COUNTIF(B2:B10,">0")

Then:

=MIN(B11:G11)+1

 

[Sarah H.]

Interesting, Don! Thank you very much for the ideas. I will play with
this.

 

[Sarah H.]

Interesting, Don! Thank you very much for the ideas. I will play with
this.

 

[Sarah H.]

"edvwvw" or "Uwe,"

While that doesn't answer the immediate question, it does have use to me in
constructing
a helper column. I had been using an OR statement for that. So thanks!

 

[Sarah H.]

"edvwvw" or "Uwe,"

While that doesn't answer the immediate question, it does have use to me in
constructing
a helper column. I had been using an OR statement for that. So thanks!

 

[Sarah H.]

Biff,

That's kind of cool! Thank you. I am trying to figure out how it works
now.

I have taken your suggestion that I simplify with a helper-column under
advisement as well.

 

[Sarah H.]

Biff,

That's kind of cool! Thank you. I am trying to figure out how it works
now.

I have taken your suggestion that I simplify with a helper-column under
advisement as well.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

You're welcome. Thanks for the feedback!