Search code examples
excelexcel-formulacountif

How to count specific values between other values

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.

Image

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)));"")