Count cells in a row with data in consecutive cells

[Ronnie1979]

I am trying to count the number of cells in a range with data in consecutive cells. The data may not match in each cell, i just need to count consecutive cells with data. If there is a blank cell between the consecutive data cells it should not be counted. Below are some examples.



[TABLE="class: grid, align: center"]
<tbody>[TR]
[TD="align: center"]1/15/18[/TD]
[TD="align: center"]1/16/18[/TD]
[TD="align: center"]1/17/18[/TD]
[TD="align: center"]1/18/18[/TD]
[TD="align: center"]1/19/18[/TD]
[TD="align: center"]Results[/TD]
[/TR]
[TR]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]<30 Late[/TD]
[TD="align: center"]>30 Late[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

I am trying to count the number of cells in a range with data in consecutive cells. The data may not match in each cell, i just need to count consecutive cells with data. If there is a blank cell between the consecutive data cells it should not be counted. Below are some examples.



[TABLE="class: grid, align: center"]
<tbody>[TR]
[TD="align: center"]1/15/18[/TD]
[TD="align: center"]1/16/18[/TD]
[TD="align: center"]1/17/18[/TD]
[TD="align: center"]1/18/18[/TD]
[TD="align: center"]1/19/18[/TD]
[TD="align: center"]Results[/TD]
[/TR]
[TR]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]<30 Late[/TD]
[TD="align: center"]>30 Late[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

Do you want an actual count of the multiple contiguous data cells or just a 1 indicating there is at least one of them? In other word, should the ??? be 1 or 2 in the table below?

[TABLE="class: grid, align: center"]
<tbody>[TR]
[TD="align: center"]1/15/18[/TD]
[TD="align: center"]1/16/18[/TD]
[TD="align: center"]1/17/18[/TD]
[TD="align: center"]1/18/18[/TD]
[TD="align: center"]1/19/18[/TD]
[TD="align: center"]Results[/TD]
[/TR]
[TR]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]<30 Late[/TD]
[TD="align: center"]>30 Late[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]NS[/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"][/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ronnie1979]

I would like a count of the multiple contiguous data cells. In the highlighted row the count would be 2 continuous groups. Thanks for your help.

 

[Rick Rothstein]

I would like a count of the multiple contiguous data cells. In the highlighted row the count would be 2 continuous groups. Thanks for your help.

Give this UDF (user defined function) a try...

[table="width: 500"]
[tr]
	[td]Function ConsecutiveCount(Rng As Range) As Long
  Dim X As Long, Pattern As String
  Pattern = Space(Rng.Cells.Count)
  For X = 1 To Rng.Cells.Count
    If Len(Rng(X)) Then Mid(Pattern, X) = "X"
  Next
  ConsecutiveCount = UBound(Filter(Split(Pattern), "XX")) + 1
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ConsecutiveCount just like it was a built-in Excel function. For example, to count the consecutive contiguous cells in the range A1:L1...

=ConsecutiveCount(A1:L1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[barry houdini]

Assuming data in A2:Z2 you can use this formula

=SUM(IF(FREQUENCY(IF(A2:Z2<>"",COLUMN(A2:Z2)),IF(A2:Z2="",COLUMN(A2:Z2)))>1,1))

confirm with CTRL+SHIFT+ENTER

 

[Ronnie1979]

Rick the first time I used the macro (UDF) it worked but then it stopped and i get a return of #Name ? Also i had to input the UDF into the "view code" function on the developer tab. When i pressed Alt F11 it just turned the scene brightness down. Thanks for you help.

 

[Rick Rothstein]

Rick the first time I used the macro (UDF) it worked but then it stopped and i get a return of [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NAME]#NAME [/URL] ? Also i had to input the UDF into the "view code" function on the developer tab. When i pressed Alt F11 it just turned the scene brightness down. Thanks for you help.

The "View Code" item on the Developer tab is fine to use to get to the VB editor. As for the brightness being turned down... it sounds like you have one of those keyboards where you have to press an "fn" (function) button to activate the function keys or else they perform some kind of computer action by default. As for the #NAME ! error... I think that would only have happened if you accidentally deleted part of the function's name or the entire function itself (the #NAME ! error means Excel cannot see any procedure by the name you typed into the cell). Try deleting whatever you have from your previous attempt to install the UDF and then copy/paste the code into the module again and see if it works then. You might also give Barry Houdini's formula (Message #5 ) a try instead as that appears to do what you want directly without using any VB code.