I’m a beginner with google sheets/exel formulas so i’m having trouble to count how many rows there are until I get a specific value, and this, multiple times in a same column.
Example bellow, I would like to set an ARRAYFORMULA in B1 so I can get those results:
| Row\Column | A | B |
|---|---|---|
| 1 | Value | Result |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 0 | 1 |
| 5 | 1 | 0 |
| 6 | 0 | 4 |
| 7 | 0 | 3 |
| 8 | 0 | 2 |
| 9 | 0 | 1 |
| 10 | 1 | 0 |
The purpose is to count raws untill I get the value "1" in column A and put the result in column B:
So obviously I could use something like :
B2=IF(A2=0;IF(ISNUMBER(B1);B1+1;1);0)
And deploy this formula to every cells in the column B
But I really wish I could get an ARRAYFORMULA in B1 to do so.
I tried to use:
B1=ARRAYFORMULA({"Result";IF(A2:A=0; COUNTIFS(A2:A; 0; ROW(A2:A);"<="&ROW(A2:A)); 0)})
But as you can presume, this won’t work because A2 stay the value from where the count is done. Besides, the counting is reversed.
We would get something like , which is not what I want:
| Row\Column | A | B |
|---|---|---|
| 1 | Value | Result |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 0 | 2 |
| 5 | 1 | 0 |
| 6 | 0 | 3 |
| 7 | 0 | 4 |
| 8 | 0 | 5 |
| 9 | 0 | 6 |
| 10 | 1 | 0 |
I really struggle to increment values with arrayformulas so if anyone has done something similar, help would be appreciated.
>Solution :
You may try:
=let(Γ;index(A:A;index(match(2;1/(A:A<>""))));
map(A2:Γ;lambda(Σ;if(Σ=1;0;xmatch(1;offset(Σ;1;):Γ)))))
