I am trying to find a formula that will count the number of consecutive 0's in a row until it finds a number >0 and then it stops counting.
Example:
Starting in cell A1
Data Type - 0 0 0 0 0 1 3 5 0 7
The formula should count 5
Thanks for helping!
I am trying to find a formula that will count the number of consecutive 0's in a row until it finds a number >0 and then it stops counting.
Example:
Starting in cell A1
Data Type - 0 0 0 0 0 1 3 5 0 7
The formula should count 5
Thanks for helping!
This appears to work.
Formula:Please Login or Register to view this content.
If posting code please use code tags, see here.
Thank you Norie! Looking to see if I can make this work. Do I replace the entire row of data (ex: A1:A8) instead of the "1:1" in your formula. I need it to look at the entire row of data to determine how many consecutive 0's there are from left to right (just the first group of consecutive 0's that the formula finds).
The formula I posted is looking at the entire row, if you change 1:1 to A1:A8 it will be looking at 8 cells in a column not a row.
Norie it could possibly go the opposite way.
My end goal is to count the number of days something was selling. I want it to start counting with the first actual number >0.
So if the first few days there were 0 sold, I wouldn't want those days counted.
EX: 0 0 0 0 0 1 3 5 0 7
The answer I would be looking for here is 5 (5 days selling)
Why not use COUNTIF?
Formula:Please Login or Register to view this content.
I've tried doing that first. I need the 0's to be included once the item was selling. That's why I thought I could find a formula to count just the consecutive 0's up to the point that a number >0 was found.
Thank you .
Can't you just use the first formula I posted and subtract it's result from the total no of cells?
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks