How to count number of consecutive wet days in the a rainfall table?

[yabi100]

I have a rainfall table and the google sheet link is provided below. For each year, I want to count number of days, fulfilling following both two conditions:

1) Number of days where rainfall is more than or equal to 1 mm.

2) Count only those days which are not single.
In other words, I want to count those days where at least two consecutive days are more than 1 mm.
This is called consecutive wet days in hydrology and I have to find a way in Excel to calculate them.
For example, in following part of the table as attached images:
Row 38 and 40 should not be counted because they are single, but rows 42 to 46 (5 days) should be counted, as they are consecutive and more than 1.
I have pasted the table into following link

https://docs.google.com/spreadsheets/d/1pePQbz34OqCFlTI5EwKSFIk2_H7cPNUEzFseDvK9E_w/edit?usp=sharing

I have to do it for each year separately. Separating years will be easy by putting a countif on year. But don't know how to perform consecutive days count.

Is there any way in Excel to perform this calculation?

 

[Jeffrey Mahoney]

Here is some more analysis formulas. Enter in dates into J2:K4 to limit each row by date range. Enter in wet days for the count to calculate how many wet days there => the provided wet days. The maximum is also using the date ranges provided.

As far as your 100% and 90% only showing values: you must have a large number of days with zero rainfall. You may want to get a better resolution, down to .99, .98, etc.

Yabi100.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Rainfall (mm)	Year	Wet Days	Count		Percentile	Wet Days		First Date	Last Date	Wet Days	Count	Maximum
2	1/1/2020	7	2020	FALSE	0		1	260		1/1/2020	12/31/2020	6	286	65
3	1/2/2020	0	2020	FALSE	0		0.9	102		1/1/2021	12/31/2021	10	315	260
4	1/3/2020	31	2020	TRUE	13		0.8	57		1/1/2022	12/31/2022	20	175	103
5	1/4/2020	8	2020	TRUE	12		0.7	42
6	1/5/2020	38	2020	TRUE	11		0.6	32
7	1/6/2020	31	2020	TRUE	10		0.5	24
8	1/7/2020	27	2020	TRUE	9		0.4	17
9	1/8/2020	1	2020	TRUE	8		0.3	11
10	1/9/2020	36	2020	TRUE	7		0.2	6
11	1/10/2020	25	2020	TRUE	6		0.1	2
12	1/11/2020	4	2020	TRUE	5		0	0
13	1/12/2020	11	2020	TRUE	4
14	1/13/2020	27	2020	TRUE	3
15	1/14/2020	9	2020	TRUE	2
16	1/15/2020	19	2020	TRUE	1
17	1/16/2020	3	2020	FALSE	0
18	1/17/2020	0	2020	FALSE	0
19	1/18/2020	3	2020	TRUE	10
20	1/19/2020	38	2020	TRUE	9
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=YEAR(A2)
D2:D20	D2	=AND(B2>=1,SUMIFS(B:B,A:A,A2+1,B:B,">="&1))
E2:E20	E2	=IF(D2=TRUE,COUNTA(OFFSET($D2,0,0,MATCH(FALSE,$D2:$D$1000000,0)-1,1)),0)
M2:M4	M2	=COUNTIFS($E:$E,">="&L2,$A:$A,">="&J2,$A:$A,"<="&K2)
N2:N4	N2	=MAXIFS(E:E,A:A,">="&J2,A:A,"<="&K2)
H2:H12	H2	=PERCENTILE($E$2:$E$1644,G2)

 

[yabi100]

Dear Jeffrey
Will read your reply tomorrow morning. Have a good night.

 

[yabi100]

Thanks a lot. My data are 50 years from 1971 to 2021.
Let me read tomorrow and reach you.
Again thanks for all your valuable support.

 

[Jeffrey Mahoney]

OK, so I tweaked the Contiguous Wet Days column. It now only shows one number per set of wet days. This will improve your stats. I changed my date ranges to reflect a water year that the agencies use in their calculations; usually by the USGS.

I altered the Count columns in the stat area. "Count >=" shows the count of wet days greater than or equal to the provided value. "Count =" shows the count of wet days equal to the provided value. I also added "Min > 0" which shows the minimum wet days over zero.

Yabi100.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Rainfall (mm)	Year	Wet Days	Contiguous Wet Days		First Date	Last Date	Wet Days	Count >=	Count =	Max	Min > 0
2	1/1/2020	7	2020	FALSE	0		10/1/2020	9/30/2021	7	6	1	260	7
3	1/2/2020	0	2020	FALSE	0		10/1/2021	9/30/2022	12	7	1	67	4
4	1/3/2020	31	2020	TRUE	13		10/1/2022	9/30/2023	20	6	0	103	1
5	1/4/2020	8	2020	TRUE	0
6	1/5/2020	38	2020	TRUE	0
7	1/6/2020	31	2020	TRUE	0
8	1/7/2020	27	2020	TRUE	0
9	1/8/2020	1	2020	TRUE	0
10	1/9/2020	36	2020	TRUE	0
11	1/10/2020	25	2020	TRUE	0
12	1/11/2020	4	2020	TRUE	0
13	1/12/2020	11	2020	TRUE	0
14	1/13/2020	27	2020	TRUE	0
15	1/14/2020	9	2020	TRUE	0
16	1/15/2020	19	2020	TRUE	0
17	1/16/2020	3	2020	FALSE	0
18	1/17/2020	0	2020	FALSE	0
19	1/18/2020	3	2020	TRUE	10
20	1/19/2020	38	2020	TRUE	0
21	1/20/2020	8	2020	TRUE	0
22	1/21/2020	12	2020	TRUE	0
23	1/22/2020	33	2020	TRUE	0
24	1/23/2020	29	2020	TRUE	0
25	1/24/2020	3	2020	TRUE	0
26	1/25/2020	38	2020	TRUE	0
27	1/26/2020	9	2020	TRUE	0
28	1/27/2020	23	2020	TRUE	0
29	1/28/2020	22	2020	FALSE	0
30	1/29/2020	0	2020	FALSE	0
31	1/30/2020	10	2020	TRUE	21
32	1/31/2020	8	2020	TRUE	0
33	2/1/2020	18	2020	TRUE	0
34	2/2/2020	14	2020	TRUE	0
35	2/3/2020	38	2020	TRUE	0
36	2/4/2020	27	2020	TRUE	0
37	2/5/2020	35	2020	TRUE	0
38	2/6/2020	32	2020	TRUE	0
39	2/7/2020	12	2020	TRUE	0
40	2/8/2020	24	2020	TRUE	0
41	2/9/2020	30	2020	TRUE	0
42	2/10/2020	10	2020	TRUE	0
43	2/11/2020	34	2020	TRUE	0
44	2/12/2020	26	2020	TRUE	0
45	2/13/2020	36	2020	TRUE	0
46	2/14/2020	27	2020	TRUE	0
47	2/15/2020	10	2020	TRUE	0
48	2/16/2020	32	2020	TRUE	0
49	2/17/2020	2	2020	TRUE	0
50	2/18/2020	11	2020	TRUE	0
51	2/19/2020	29	2020	TRUE	0
52	2/20/2020	34	2020	FALSE	0
53	2/21/2020	0	2020	FALSE	0
54	2/22/2020	3	2020	TRUE	10
55	2/23/2020	19	2020	TRUE	0
56	2/24/2020	16	2020	TRUE	0
57	2/25/2020	36	2020	TRUE	0
58	2/26/2020	4	2020	TRUE	0
59	2/27/2020	30	2020	TRUE	0
60	2/28/2020	33	2020	TRUE	0
61	2/29/2020	7	2020	TRUE	0
62	3/1/2020	30	2020	TRUE	0
63	3/2/2020	20	2020	TRUE	0
64	3/3/2020	35	2020	FALSE	0
65	3/4/2020	0	2020	FALSE	0
66	3/5/2020	12	2020	TRUE	49
67	3/6/2020	23	2020	TRUE	0
68	3/7/2020	14	2020	TRUE	0
69	3/8/2020	8	2020	TRUE	0
70	3/9/2020	17	2020	TRUE	0
71	3/10/2020	31	2020	TRUE	0
72	3/11/2020	15	2020	TRUE	0
73	3/12/2020	3	2020	TRUE	0
74	3/13/2020	33	2020	TRUE	0
Sheet1

Cell Formulas
Range	Formula
C2:C74	C2	=YEAR(A2)
D2:D74	D2	=AND(B2>=1,SUMIFS(B:B,A:A,A2+1,B:B,">="&1))
E2:E74	E2	=IF(AND(D2=TRUE,$D1=FALSE),COUNTA(OFFSET($D2,0,0,MATCH(FALSE,$D2:$D$1000000,0)-1,1)),0)
J2:J4	J2	=COUNTIFS($E:$E,">="&$I2,$A:$A,">="&$G2,$A:$A,"<="&$H2)
K2:K4	K2	=COUNTIFS($E:$E,"="&$I2,$A:$A,">="&$G2,$A:$A,"<="&$H2)
L2:L4	L2	=MAXIFS($E:$E,$A:$A,">="&$G2,$A:$A,"<="&$H2)
M2:M4	M2	=MINIFS($E:$E,$E:$E,">"&0,$A:$A,">="&$G2,$A:$A,"<="&$H2)

 

[felixstraube]

Here another option. Is this what you need?:
Data extends till row 18629.
Question is what happens if you have a wet period just between 2 years. Lets say it starts to rain on the 28 of December and it rains till 3 of January (inclusive).
What the "# of wet periods" formula currently does is count the period as of 1972.
And the "# of days of longest wet period" would show 4 if it would be the longest wet period in 1971, and 7 for 1972.

Book6.xlsx
	A	B	C	D	E	F	G	H
1	Date	Rainfall [mm]	Helper column 1	Helper column 2		Year	# or wet periods (2 or more consecutive days of 1 mm or more)	# of days of the longest wet period
2	01/01/1971	-	-	-		1971	34	6
3	02/01/1971	-	-	-		1972	34	6
4	03/01/1971	25,6	1	1		1973	37	7
5	04/01/1971	-	-	-		1974	30	4
6	05/01/1971	-	-	-		1975	45	7
7	06/01/1971	73,6	1	1		1976	30	8
8	07/01/1971	-	-	-		1977	31	5
9	08/01/1971	-	-	-		1978	44	6
10	09/01/1971	39,2	1	1		1979	35	8
11	10/01/1971	-	-	-		1980	32	4
12	11/01/1971	-	-	-		1981	38	4
13	12/01/1971	-	-	-		1982	38	4
14	13/01/1971	-	-	-		1983	33	6
15	14/01/1971	-	-	-		1984	29	4
16	15/01/1971	68,6	1	1		1985	35	6
17	16/01/1971	-	-	-		1986	29	6
18	17/01/1971	-	-	-		1987	35	7
19	18/01/1971	-	-	-		1988	33	5
20	19/01/1971	-	-	-		1989	38	8
21	20/01/1971	16,6	1	-		1990	37	6
22	21/01/1971	9,9	2	2		1991	40	6
23	22/01/1971	-	-	-		1992	27	6
24	23/01/1971	-	-	-		1993	32	7
25	24/01/1971	52,5	1	1		1994	35	4
26	25/01/1971	-	-	-		1995	33	6
27	26/01/1971	-	-	-		1996	40	7
28	27/01/1971	41,6	1	1		1997	31	5
29	28/01/1971	-	-	-		1998	30	8
30	29/01/1971	-	-	-		1999	34	5
31	30/01/1971	29,8	1	1		2000	32	6
32	31/01/1971	-	-	-		2001	36	5
33	01/02/1971	-	-	-		2002	33	5
34	02/02/1971	-	-	-		2003	38	4
35	03/02/1971	-	-	-		2004	39	5
36	04/02/1971	16,7	1	1		2005	32	6
37	05/02/1971	-	-	-		2006	39	6
38	06/02/1971	-	-	-		2007	32	5
39	07/02/1971	-	-	-		2008	35	5
40	08/02/1971	35,7	1	1		2009	39	7
41	09/02/1971	-	-	-		2010	38	6
42	10/02/1971	73,1	1	1		2011	37	8
43	11/02/1971	-	-	-		2012	41	5
44	12/02/1971	-	-	-		2013	30	6
45	13/02/1971	-	-	-		2014	30	7
46	14/02/1971	-	-	-		2015	30	4
47	15/02/1971	46,4	1	1		2016	34	7
48	16/02/1971	-	-	-		2017	35	11
49	17/02/1971	-	-	-		2018	34	6
50	18/02/1971	-	-	-		2019	38	6
51	19/02/1971	-	-	-		2020	35	8
52	20/02/1971	-	-	-		2021	31	5
53	21/02/1971	-	-	-
54	22/02/1971	-	-	-
55	23/02/1971	4,2	1	-
56	24/02/1971	27,2	2	2
57	25/02/1971	-	-	-
58	26/02/1971	76,6	1	-
59	27/02/1971	46,3	2	2
60	28/02/1971	-	-	-
61	01/03/1971	-	-	-
Sheet1

Cell Formulas
Range	Formula
C2:C61	C2	=IF((B2>=1), N(C1)+1, 0)
D2:D61	D2	=C2*(C3=0)
G2:G52	G2	=SUM((YEAR($A$2:$A$18629)=F2)*($D$2:$D$18629>1))
H2:H52	H2	=MAX((YEAR($A$2:$A$18629)=F2)*($C$2:$C$18629))

 

[Jeffrey Mahoney]

The formula I have in column already accounts for years. See the example below with November 30 starting a 38 day wet period

Yabi100.xlsm
	A	B	C	D	E
1	Date	Rainfall (mm)	Year	Wet Days	Contiguous Wet Days
335	11/29/2020	0	2020	FALSE	0
336	11/30/2020	18	2020	TRUE	38
337	12/1/2020	3	2020	TRUE	0
338	12/2/2020	5	2020	TRUE	0
339	12/3/2020	32	2020	TRUE	0
340	12/4/2020	27	2020	TRUE	0
341	12/5/2020	16	2020	TRUE	0
342	12/6/2020	30	2020	TRUE	0
343	12/7/2020	38	2020	TRUE	0
344	12/8/2020	3	2020	TRUE	0
345	12/9/2020	3	2020	TRUE	0
346	12/10/2020	19	2020	TRUE	0
347	12/11/2020	19	2020	TRUE	0
348	12/12/2020	18	2020	TRUE	0
349	12/13/2020	30	2020	TRUE	0
350	12/14/2020	11	2020	TRUE	0
351	12/15/2020	21	2020	TRUE	0
352	12/16/2020	6	2020	TRUE	0
353	12/17/2020	21	2020	TRUE	0
354	12/18/2020	26	2020	TRUE	0
355	12/19/2020	13	2020	TRUE	0
356	12/20/2020	13	2020	TRUE	0
357	12/21/2020	19	2020	TRUE	0
358	12/22/2020	16	2020	TRUE	0
359	12/23/2020	10	2020	TRUE	0
360	12/24/2020	29	2020	TRUE	0
361	12/25/2020	20	2020	TRUE	0
362	12/26/2020	17	2020	TRUE	0
363	12/27/2020	27	2020	TRUE	0
364	12/28/2020	24	2020	TRUE	0
365	12/29/2020	2	2020	TRUE	0
366	12/30/2020	1	2020	TRUE	0
367	12/31/2020	36	2020	TRUE	0
368	1/1/2021	21	2021	TRUE	0
369	1/2/2021	37	2021	TRUE	0
370	1/3/2021	23	2021	TRUE	0
371	1/4/2021	31	2021	TRUE	0
372	1/5/2021	28	2021	TRUE	0
373	1/6/2021	29	2021	TRUE	0
374	1/7/2021	10	2021	FALSE	0
375	1/8/2021	0	2021	FALSE	0
376	1/9/2021	7	2021	TRUE	56
377	1/10/2021	31	2021	TRUE	0
378	1/11/2021	31	2021	TRUE	0
379	1/12/2021	1	2021	TRUE	0
380	1/13/2021	8	2021	TRUE	0
381	1/14/2021	28	2021	TRUE	0
382	1/15/2021	13	2021	TRUE	0
383	1/16/2021	14	2021	TRUE	0
384	1/17/2021	2	2021	TRUE	0
385	1/18/2021	32	2021	TRUE	0
386	1/19/2021	8	2021	TRUE	0
387	1/20/2021	25	2021	TRUE	0
388	1/21/2021	9	2021	TRUE	0
389	1/22/2021	26	2021	TRUE	0
390	1/23/2021	5	2021	TRUE	0
391	1/24/2021	12	2021	TRUE	0
392	1/25/2021	10	2021	TRUE	0
393	1/26/2021	12	2021	TRUE	0
394	1/27/2021	36	2021	TRUE	0
395	1/28/2021	37	2021	TRUE	0
396	1/29/2021	15	2021	TRUE	0
397	1/30/2021	26	2021	TRUE	0
398	1/31/2021	14	2021	TRUE	0
Sheet1

Cell Formulas
Range	Formula
C335:C398	C335	=YEAR(A335)
D335:D398	D335	=AND(B335>=1,SUMIFS(B:B,A:A,A335+1,B:B,">="&1))
E335:E398	E335	=IF(AND(D335=TRUE,$D334=FALSE),COUNTA(OFFSET($D335,0,0,MATCH(FALSE,$D335:$D$1000000,0)-1,1)),0)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$E$1644	D335:D398
Print_Area	=Sheet1!$A$1:$E$1644	D335:D398

 

[yabi100]

Dear Felix
Thank you very much for your very genius solution. Accept my appology if this is a basic question, but I couldn't understand the meaning of the formula in D2, that is =C2*(C3=0). I am using Excel for a long time, but this is the first time I am seeing such a formula. I even don't know the name this type of formula to search in Google. Will be very happy to read a short note for this formula and it's name. Althought I admit, what ever it is, it is a great formula and the result is what I am exactly looking for.

 

[yabi100]

Dear Jeffrey
Thanks for your reply. I haven't checked your answer yet, but it seams it is doing what I want. Let me check it and come bck again. I appreciate your time and effort to answer to my question.

 

[yabi100]

You mentioned:
Question is what happens if you have a wet period just between 2 years. Lets say it starts to rain on the 28 of December and it rains till 3 of January (inclusive).
What the "# of wet periods" formula currently does is count the period as of 1972.
And the "# of days of longest wet period" would show 4 if it would be the longest wet period in 1971, and 7 for 1972.

This is a real question but for the time being, it is not my concern. My professor asked me to zoom on "years" at this moment. Maybe this will be the next subject to tackle.

 

[felixstraube]

the meaning of the formula in D2, that is =C2*(C3=0)

Hi Yabi100.

C3 = 0

Checks if C3 is equal to 0, and it returns TRUE or FALSE. But if you multiply this boolean result by any number it turns the TRUE into 1 and FALSE into 0.
So basically we check if the next day there has been rainfall and if so we take the number from C2. That is we get only the max value from every wet period.

It would be the same as

IF(C3=0, C2, 0)

Hope this makes sense.