SUM/COUNT of value 1 at the end of set

[ibmy]

Hi,

Data contain just 2 value which are 1 and 0.

01 set.xlsx
	A	B
1	Data	Result
2	0
3	1	1
4	0
5	0
6	1
7	1
8	1	3
9	0
10	1
11	1	2
12	0
13	0
14	1	1
15	0
16	0
17	0
Sheet3

Thanks advance.

 

[Michael M]

Would you like to tell us what you actually want to do ??
Maybe like

=COUNTIF(A2:A17,1)

 

[ibmy]

@Michael M
Sample of 50-100k row set data:

01 set.xlsx
	A	B	C	D	E	F	G
35	08.10.2021 05:02:52.264 GMT+0800	1.15563	1.1553	1	0	11
36	08.10.2021 05:03:04.109 GMT+0800	1.15563	1.1553	0	0
37	08.10.2021 05:03:04.214 GMT+0800	1.15561	1.15533	1	1	1	1
38	08.10.2021 05:03:04.371 GMT+0800	1.15563	1.15532	1	1	2
39	08.10.2021 05:03:14.825 GMT+0800	1.15563	1.15535	0	1		3
40	08.10.2021 05:03:23.223 GMT+0800	1.15562	1.15535	1	0	1
41	08.10.2021 05:03:28.842 GMT+0800	1.15562	1.15537	0	1		1
42	08.10.2021 05:03:35.654 GMT+0800	1.15562	1.15539	0	1		2
43	08.10.2021 05:03:41.993 GMT+0800	1.15558	1.15539	1	0	1
44	08.10.2021 05:03:45.004 GMT+0800	1.15558	1.15538	0	1		1
45	08.10.2021 05:04:11.278 GMT+0800	1.15558	1.15537	0	1		2
46	08.10.2021 05:04:42.262 GMT+0800	1.15562	1.15537	1	0	1
47	08.10.2021 05:04:50.305 GMT+0800	1.15562	1.15539	0	1		1
48	08.10.2021 05:04:50.515 GMT+0800	1.15562	1.15538	0	1
49	08.10.2021 05:04:51.255 GMT+0800	1.1556	1.15539	1	1	1
50	08.10.2021 05:04:55.111 GMT+0800	1.15561	1.15537	1	1	2
Sheet1

Cell Formulas
Range	Formula
D35:E50	D35	=IF(B35<>B34,1,0)
F35:G50	F35	=IF(AND(D34=0,D35<>0),D35,IF(AND(D36=0,D35<>0),ROW(F35)-IFERROR(MATCH(1E+100,F$2:F34),0),""))

The actual data are Column A,B and C
Then I would like analyze the data :
As formula Column D and E for Column B and C respectively. =IF(B3<>B2,1,0) and =IF(C3<>C2,1,0)
Then I use the formula in Column F and G which I ask 1 year ago Show Starting (Number 1) and Max only
The formula works great, then I want to achieved show starting start of set and max but now I no need anymore show starting start of set.
I tried to modify he formula to meet my needs for current analysis but did not work.
Hopefully, with simpler formula, the formula run faster on my old laptop which heavy to handle the set of data which contain 50-100k row data.

 

[Peter_SSs]

Sample of 50-100k row set data:

With that much data, I would suggest a macro. With post #3 I didn't really understand what part, if any you wanted help with but for the layout in post #1, try this

Sub Result()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  b(1, 1) = "Result"
  For i = 2 To UBound(a)
    If a(i, 1) = 1 Then
      k = k + 1
    Else
      If a(i - 1, 1) = 1 Then
        b(i - 1, 1) = k
        k = 0
      End If
    End If
  Next i
  Range("B1").Resize(UBound(b)).Value = b
End Sub