I have around 1500 rows such as shown below. I want to sum the 1s that appear consecutively in this row.
0 0 1 1 0 1 1 1 1 0 1 0 1 0 1 1 0
As can be seen, the value 1 appears consecutively 2 times then 4 times and then finally 2 times. So, the output should be 2+4+2=8
I have came close the solution by using
Formula:
FREQUENCY(IF(A5:Q5>0,COLUMN(A5:Q5)),IF(A5:Q5=0,COLUMN(A5:Q5)))
. Now, the only part left is conditional sum on this frequency formula array where the value is greater than 1.
Simple sum on this frequency array i.e.
Formula:
SUM(FREQUENCY(IF(A5:Q5>0,COLUMN(A5:Q5)),IF(A5:Q5=0,COLUMN(A5:Q5))))
gives me 10 as it takes into accout the two non-consecutive 1s in the row as well.
Thanks.
Bookmarks