I’m having a hard time figuring this one out.
I have a short list of numbers in column C (C4 to C19).
Those numbers can range from 0 to anything. Never empty.
In column D is a date, in column E is an offset, but this is not relevant to the problem.
I would like in column F, the count of the value zero, before that line, until we find a non-zero value above that.
So, let’s say F18. I want to know the count of zeroes in C before C18. The answer is 3.
For F17, the answer is 2 (C16 and C15).
And so on.
I did try to figure it out with some COUNTIFS and INDEX…. but I’m lost.
>Solution :
This one does it the other way around (top to bottom):
=IFERROR(SCAN(0,C4:C19,LAMBDA(a,v,IF(v=0,a+1,0))),"")
I hope I’m not breaking any rules, but here is it in French:
=SIERREUR(SCAN(0;C4:C19;LAMBDA(a;v;SI(v=0;a+1;0)));"")