I have the following table. I am looking for a formula to count consecutive 1's where each additional 1 in a string of 1's increases it's total count by 1
Ex. two consecutive 1's yield 3 (1 2).
Ex. three consecutive 1's yield 6 (1 2 3).
Ex. Two consecutive 1's and one non-consecutive 1 yields 4 (1 2 1)'
| Col 1 | Col 2 | Col 3 | Col 4 | Col 5 | Score |
|---|---|---|---|---|---|
| 1 | 0 | 0 | 0 | 0 | 1 |
| 1 | 0 | 0 | 0 | 1 | 2 |
| 0 | 1 | 1 | 0 | 0 | 3 |
| 1 | 1 | 0 | 0 | 1 | 4 |
| 0 | 1 | 1 | 1 | 0 | 6 |
CodePudding user response：
Try something like:
Formula in F2:
=SUM(SCAN(0,A2:E2,LAMBDA(a,b,IF(b,a b,0))))
CodePudding user response：
For Older versions that do not have SCAN and LAMBDA we need to use FREQUENCY and the formula (n / 2)(first number last number)
=SUM((FREQUENCY(IF(A2:E2=1,COLUMN(A2:E2)),IF(A2:E2=0,COLUMN(A2:E2)))/2)*(1 FREQUENCY(IF(A2:E2=1,COLUMN(A2:E2)),IF(A2:E2=0,COLUMN(A2:E2)))))
In those older versions, this will require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode. The use of SUMPRODUCT in place of SUM MAY negate that need, but I do not have an older version to test.
