Excel using SUMPRODUCT to find number of times a value appears in a column and in python-CodePudding

So I have this excel sheet where there is one column and filled with 1 and 0's

the 4 represents that the maximum number of 1's in the column and I dont know how to get that number. Iam wondering if there is a way to get it to return something like this in excel:

So that there is a new column after the number stops repeating and it will tell you how many times it repeats.

My idea to do this with python is something like this but I am not there yet.

listas = [1,1,0,1,0,1,1,1,1,0]
new_column = []
count = 1
for i in range(len(listas)):
   print(listas[i])
   if listas[i] == 1:
    
    new_column.append(count)
    if listas[i 1] == 1:
        count = count   1
        new_column.append(count)
    # so if the first integer is 1 check the one in the next position and if 1 coninute checking until 0

so that the new_column is [2,0,1,0,4,0] to return 0 or na so its just empty

How would I accomplish this in excel or python?

CodePudding user response：

The standard approach in Excel would be to use the FREQUENCY function:

=MAX(FREQUENCY(IF(MyRange,ROW(MyRange)),IF(1-MyRange,ROW(MyRange))))

which may or may not require committing with CTRL SHIFT ENTER, depending on your version of Excel.

CodePudding user response：

Put this formula in B2 and fill down: =LET(x,IF(A2=0,SUM(A$1:A1)-SUM(B$1:B1),0),IF(x=0,"",x))