Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Text Data | New Users to Excel | |||
Sum / count data from text string with delimiter | Excel Worksheet Functions | |||
how can i count text in a ranges of data | Excel Worksheet Functions | |||
Group and Count Text Data More Efficiently | Excel Worksheet Functions | |||
count G4:G51 for critera only if D4:D51 contains text data | Excel Worksheet Functions |