calculating the number of appearances in certain threshold

Hi,

I have 2 columns.

Column A has the number '0' and '1'.
Column B contains random numbers.

I need to calculate how many times the number '1' appear in column A for everytime the sum of the numbers in column B reach a certain treshold. Lets say the treshold is 100, I need to calculate how many times the number '1' appear in column A everytime the sum of the numbers in Column B reach 100.


for example with a treshold of 100:

A B
0 25
0 30
1 15
0 17
1 20 <-- here the sum reaches 100 for the first time (107)
1 10
0 18
1 17
1 26
1 20
0 31 <-- here the sum reaches 100 for the second time (122)
0 60
1 50 <-- here the sum reaches 100 for the third time (110)
... ...

In this case, the first treshold has 2 '1's, the second treshold has 4 '1's and the third treshold has 1 '1'. It would be great if the results are shown in a third column for example:

Column C
2
4
1
...

Thank you in advance!

Use a helper column to sovle it.

C1=IF(B1>=100,0,B1)
C2=IF(C1+B2>=100,0,C1+B2)
copied down.

D1

Please Login or Register  to view this content.

copied down.

thank you so much! it works wonderfully!