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 :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(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

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
Microsoft MVP Excel
SalesAid Software

"Sarah H." wrote in message
...
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 :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(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

Can you use

=MIN(E2:I2)

This will ignore Text and cells without an entry

edvwvw

Sarah H. wrote:
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:D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUN TIF(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

--
Message posted via http://www.officekb.com

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


--
Biff
Microsoft Excel MVP


"Sarah H." wrote in message
...
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 :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(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

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

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Sarah H." wrote in message
...
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 :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(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

"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

"edvwvw via OfficeKB.com" <u42512@uwe wrote in message
news:9643764bd3b3d@uwe...
Can you use

=MIN(E2:I2)

This will ignore Text and cells without an entry

edvwvw

Sarah H. wrote:
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:D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COU NTIF(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

--
Message posted via http://www.officekb.com

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

"T. Valko" wrote in message
...
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


--
Biff
Microsoft Excel MVP


"Sarah H." wrote in message
...
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 :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Sarah H." wrote in message
...
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

"T. Valko" wrote in message
...
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


--
Biff
Microsoft Excel MVP


"Sarah H." wrote in message
...
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 :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(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