Count number of cells between two cells with a positive value.

1. Count number of cells between two cells with a positive value.

I'm desperately looking for a formula which will return the same values I have in line 9 of my example.xls sheet.

It simply returns the number of cells between two cells in a timeline, which actually have values (not zero), like in line 4.

So as an example: there is a value in E4 and then zeros until J4. I need the cells in E9 to I9 to tell me that there are 5 periods between the two.

Thanks a lot for your help.
example (1).xlsx

2. Re: Count number of cells between two cells with a positive value.

Hi,

Not quite sure how you arrived at your value of 0 for Jan, so I just added a simple clause which will default the first return to 0, but this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in D9 and copied across should match your desired results:

=IF(COLUMNS(\$A:A)=1,0,1+INDEX(FREQUENCY(IF(\$D\$4:\$Z\$4=0,COLUMN(\$D\$4:\$Z\$4)),IF(\$D\$4:\$Z\$4<>0,COLUMN(\$D\$4:\$Z\$4))),1+COUNTIF(\$D\$4:D\$4,">0")))

Regards

3. Re: Count number of cells between two cells with a positive value.

Very impressed! thank you!

4. Re: Count number of cells between two cells with a positive value.

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)