Count every 5 row that contains text or data
 

I'm trying to come up with the least amount of code to count every 5th
cell that contains text or data in column A starting with row 1. In
other words, the following would return a count of 3 records:

Column A
1 John Doe
2 Jiffy's
3 101Main
4 Anywhere, MI 79999
5
6 Jim Johnson
2 Jim's Trim
3 1019 Gilmer Rd
4 Jackson, TX 79555
6
7 David Smith
8 Smith MFG
9 109 E Whatley
10 Smithville, Fl 78666
11
12
13
14
15
16

Any help with the code will be greatly appreciated! mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=401529

Count every 5 row that contains text or data
 

Not sure I understand the data, but here goes

=SUMPRODUCT(--(MOD(ROW(A1:A1000),5)=1),--(A1:A1000<""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mikeburg" wrote in
message ...

I'm trying to come up with the least amount of code to count every 5th
cell that contains text or data in column A starting with row 1. In
other words, the following would return a count of 3 records:

Column A
1 John Doe
2 Jiffy's
3 101Main
4 Anywhere, MI 79999
5
6 Jim Johnson
2 Jim's Trim
3 1019 Gilmer Rd
4 Jackson, TX 79555
6
7 David Smith
8 Smith MFG
9 109 E Whatley
10 Smithville, Fl 78666
11
12
13
14
15
16

Any help with the code will be greatly appreciated! mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=401529

Count every 5 row that contains text or data
 

Do you really need code:-

Only caveat is that you can't use whole column references, so I've used 1000
as an arbitrary size.

=SUMPRODUCT(--(A1:A1000<""),--(MOD(ROW(A1:A1000),5)=0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"mikeburg" wrote in
message ...

I'm trying to come up with the least amount of code to count every 5th
cell that contains text or data in column A starting with row 1. In
other words, the following would return a count of 3 records:

Column A
1 John Doe
2 Jiffy's
3 101Main
4 Anywhere, MI 79999
5
6 Jim Johnson
2 Jim's Trim
3 1019 Gilmer Rd
4 Jackson, TX 79555
6
7 David Smith
8 Smith MFG
9 109 E Whatley
10 Smithville, Fl 78666
11
12
13
14
15
16

Any help with the code will be greatly appreciated! mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=401529

Count every 5 row that contains text or data
 

I assumed that starting in row 1, every 5th row was 5,10,15,20 etc

Regards
Ken..................

"Bob Phillips" wrote in message
...
Not sure I understand the data, but here goes

=SUMPRODUCT(--(MOD(ROW(A1:A1000),5)=1),--(A1:A1000<""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mikeburg" wrote
in
message ...

I'm trying to come up with the least amount of code to count every 5th
cell that contains text or data in column A starting with row 1. In
other words, the following would return a count of 3 records:

Column A
1 John Doe
2 Jiffy's
3 101Main
4 Anywhere, MI 79999
5
6 Jim Johnson
2 Jim's Trim
3 1019 Gilmer Rd
4 Jackson, TX 79555
6
7 David Smith
8 Smith MFG
9 109 E Whatley
10 Smithville, Fl 78666
11
12
13
14
15
16

Any help with the code will be greatly appreciated! mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread:
http://www.excelforum.com/showthread...hreadid=401529

Count every 5 row that contains text or data
 

If you really need code though then one way:-

Sub CountRows()

Dim lastrw As Long
Dim x As Long
Dim cnt As Long

lastrw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
cnt = 0

For x = 5 To lastrw Step 5
If Cells(x, 1).Value < "" Then
cnt = cnt + 1
End If
Next x

MsgBox "Cnt = " & cnt
End Sub

Regards
Ken........................


"Ken Wright" wrote in message
...
Do you really need code:-

Only caveat is that you can't use whole column references, so I've used
1000 as an arbitrary size.

=SUMPRODUCT(--(A1:A1000<""),--(MOD(ROW(A1:A1000),5)=0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"mikeburg" wrote
in message ...

I'm trying to come up with the least amount of code to count every 5th
cell that contains text or data in column A starting with row 1. In
other words, the following would return a count of 3 records:

Column A
1 John Doe
2 Jiffy's
3 101Main
4 Anywhere, MI 79999
5
6 Jim Johnson
2 Jim's Trim
3 1019 Gilmer Rd
4 Jackson, TX 79555
6
7 David Smith
8 Smith MFG
9 109 E Whatley
10 Smithville, Fl 78666
11
12
13
14
15
16

Any help with the code will be greatly appreciated! mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread:
http://www.excelforum.com/showthread...hreadid=401529

Count every 5 row that contains text or data
 

Thanks everyone, y'all been a tremendous help.
Mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=401529