Count number "0" after last "1"

[Undertegnede]

Hi,

I want to be able to count 0's after the last 1 in my spreadsheet. The number i want counted is listed in my "="-column.

Anyone able to assist please?


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Your thread title indicates you would have zeros in the non-one cells, but your picture shows dashes instead. The following UDF (user defined function) will work as long as there is something in those cells (in other words, as long as the non-one cells are not blank)...

[table="width: 500"]
[tr]
	[td]Function CountAfterLastOne(HorizontalRange As Range) As Long
  Dim Dashes() As String
  Dashes = Split(Join(Application.Index(HorizontalRange.Value, 1, 0), ""), "1")
  CountAfterLastOne = Len(Dashes(UBound(Dashes)))
End Function[/td]
[/tr]
[/table]

Actually, forget the above... the following UDF (user defined function) will work whether the non-one cells have anything in them or not.

[table="width: 500"]
[tr]
	[td]Function CountAfterLastOne(HorizontalRange As Range) As Long
  On Error GoTo NoOnes
  CountAfterLastOne = HorizontalRange.Columns.Count - HorizontalRange.Find("1", , xlValues, , xlByColumns, xlPrevious, , , False).Column
NoOnes:
End Function[/td]
[/tr]
[/table]

EDIT NOTE: I should explain that the HorizontalRange argument is the range along one row encompassing your 1's and 0's or dashes or whatever. From your picture, that would be B3:AY3 for the first row of data.

 

[Undertegnede]

Hi, I can't speak of the VBA - but did you try to adapt the formula in post #4 to your actual set-up?

FormR, I am sorry for not commenting on your input. I failed to adapt the formula at first, but now I made it work in my setup. The only issue I now encountered was when a row did not have any 1's. It now returns #N/A, but for this case I would still like to get the number of 0s (I realize this was not in my example data).

Btw, the dashes in my picture were formatted 0s - I'm sorry for the confusion.

 

[Undertegnede]

Actually, forget the above... the following UDF (user defined function) will work whether the non-one cells have anything in them or not.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function CountAfterLastOne(HorizontalRange As Range) As Long
  On Error GoTo NoOnes
  CountAfterLastOne = HorizontalRange.Columns.Count - HorizontalRange.Find("1", , xlValues, , xlByColumns, xlPrevious, , , False).Column
NoOnes:
End Function[/TD]
[/TR]
</tbody>[/TABLE]

EDIT NOTE: I should explain that the HorizontalRange argument is the range along one row encompassing your 1's and 0's or dashes or whatever. From your picture, that would be B3:AY3 for the first row of data.

Rick, I appreciate your solution. I just now learned the possibility of defining functions in vba and then putting them to use as a formula or what you would call it.

I noticed that when using the function on my data starting in the b-column, it gave the desired resultat minus 1. However starting from the name column (A) it did give the result I was after.

Thanks!

 

[FormR]

The only issue I now encountered was when a row did not have any 1's. It now returns #N/A, but for this case I would still like to get the number of 0s

Hi, for that you could try this adaption.

=COLUMNS(A1:R1)-IFERROR(LOOKUP(2,1/(A1:R1=1),COLUMN(A1:R1)-MIN(COLUMN(A1:R1))+1),0)

 

[Undertegnede]

Hi, for that you could try this adaption.

=COLUMNS(A1:R1)-IFERROR(LOOKUP(2,1/(A1:R1=1),COLUMN(A1:R1)-MIN(COLUMN(A1:R1))+1),0)

Great, thanks! A minor "issue" remains - when the row with no 1's doesn't have any data in the first few columns it counts these empty cells as 0s (i guess since there is no 1 to "reset" the counter?).
Would this be a limitation of using a formula vs. vba, or is there a fix for this as well?

I could of course adjust the range according to when the different rows "started", but I'm curious to hear if there is a way to consider this case also.

 

[FormR]

A minor "issue" remains - when the row with no 1's doesn't have any data in the first few columns it counts these empty cells as 0s

Hi, you could try this, which very specifically only counts the zeros.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	14
2	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	12
3	0	0	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	7
4						1				0	0	0	0	0	0	0	0	0	9
5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	18
6			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	16
Sheet1

Cell Formulas
Range	Formula
S1	=COUNTIFS(INDEX(A1:R1,0,IFERROR(1+LOOKUP(2,1/(A1:R1=1),COLUMN(A1:R1)-MIN(COLUMN(A1:R1))+1),0)):R1,0)

 

[Rick Rothstein]

Rick, I appreciate your solution. I just now learned the possibility of defining functions in vba and then putting them to use as a formula or what you would call it.

I noticed that when using the function on my data starting in the b-column, it gave the desired resultat minus 1. However starting from the name column (A) it did give the result I was after.

Here is my UDF (user defined function) modified to overcome the problem you discovered in my original code...

[table="width: 500"]
[tr]
	[td]Function CountAfterLastOne(HorizontalRange As Range) As Long
  On Error GoTo NoOnes
  CountAfterLastOne = Range(Split(HorizontalRange.Address, ":")(1)).Column - HorizontalRange.Find("1", , xlValues, , xlByColumns, xlPrevious, , , False).Column
NoOnes:
End Function[/td]
[/tr]
[/table]