Formula for finding number of consecutive blank cells over a year - See info!

**oldtauntonian** · 03-14-2013, 04:21 PM

Have 8 years worth of a river catchment's precipitation data. There are readings taken on the hour every hour every day, laid out as below. I'm stuck on how to find a formula that could find the number of 'rain days' throughout the year i.e. the number of consecutive 24 hour periods between midnights with 0 precipitation. Any suggestions at all?

31/12/1993 19:00 0
31/12/1993 20:00 0
31/12/1993 21:00 0.047
31/12/1993 22:00 0.149
31/12/1993 23:00 0.031
01/01/1994 00:00 0.007

Cheers in advance guys

**mrice** · 03-14-2013, 05:11 PM

Suggest the following (assuming data is in columns A to C and there is a header row)

Copy column A to column E
Deduplicate column E (data - remove duplicates)

Enter =SUMIF(A:A,E2,C:C) into F2
Copy F2 down rest of column F (double click on fill handle)
Apply autofilters to columns E and F
Filter for rows where column F = 0

**oldtauntonian** · 03-15-2013, 12:02 PM

Hi mrice,

Only thing is it's two columns (i.e. the timestamp 01/01/1994 00:00 is in column A, and the precipitation data is in column B)..

**mrice** · 03-16-2013, 08:31 AM

OK try this

In C2 type =INT(A2) and copy down

Copy column C and paste special - values into column F

Remove duplicates from column F

in G2 type =SUMIF(C:C,F2,B:B) and copy down.