I have a spreadsheet with daily rainfall data listed in column A. I want to find the longest period of no rainfall - I used to have a spreadsheet somewhere that used an array formula in a column next to the rainfall data and it counted the number of consecutive zero days, so I know it's possible! Can't for the life me remember the formula I used though.

Example: in column A (daily rainfall data) there is:

0.5, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 1.5 etc

So in column B this would read as:

0, 1, 2, 0, 1, 2, 3, 0

etc, i.e. basically counting the number of zeros in column A, adding them up until a non-zero is found and starting again.

Thanks in advance
-Rob