Count days with specific balance

[PAOLO7673]

Hi everyone.
I need to count the number of days reaching a specific bank balance.
In Colum A the dates are listed, repeated dates as well
In Colum B the running balance with positive and negatives amounts.
I want to know how many days the negative balance occurred in a year( specified in a cell)
eg: in 2023 the balance below $1000 lasted 13 days.
Many thanks for helps.
paolo

 

[PAOLO7673]

@PAOLO7673 Please check, Note: Date format "MMDDYY"

Book2
	A	B	C	D	E	F	G	H	I	J
1	DATE		AMT IN/OUT	BALANCE	DAYS Negative BAL	Manual				Result
2	01/01/22		50	400				year	2023	146
3	01/03/22		-600	-200	2	2		balance limit	<=0
4	01/04/22		400	200		0
5	01/04/22		500	700		0
6	01/05/22		600	1300		0
7	01/06/22		-1400	-100	1	1
8	01/07/22		250	150		0
9	01/08/22		50	200		0
10	01/09/22		100	300		0
11	01/09/22		50	350		0
12	01/11/23		-500	-150		0
13	02/02/23		100	-50	22	22
14	02/05/23		50	0	3	3
15	06/01/23		-200	-200	116	116
16	06/06/23		200	0	5	5
17	11/30/23		100	100		0
18	11/30/23		200	300		0
19	11/30/23		350	650		0
20	11/30/23		600	1250		0
21	12/31/23		-1000	250		0
22	01/01/24		-500	-250	1	1
23	01/02/24		600	350		0
24	01/03/24		300	650		0	???
25	01/03/24		250	900		0	???
26	01/03/24		-2000	-1100	1	1	???
27	12/01/24		150	-950	333	333
28	12/01/24		100	-850	0	0
29	12/31/24		50	-800	30	30
30	12/31/24		800	0	0	0
Sheet1

Cell Formulas
Range	Formula
J2	J2	=IFERROR(MAX(FILTER($A$2:$A$30,($D$2:$D$30<=0)*(YEAR($A$2:$A$30)=$I$2)))-MIN(FILTER($A$2:$A$30,($D$2:$D$30<=0)*(YEAR($A$2:$A$30)=$I$2))),0)

Thak you Sam, but my excel version doesnt accept the filter function. Is there any other way?

 

[Sam_D_Ben]

Please try this,

=IFERROR(MAX(IF(($D$2:$D$30<=0)*(YEAR($A$2:$A$30)=$I$2), $A$2:$A$30)) - MIN(IF(($D$2:$D$30<=0)*(YEAR($A$2:$A$30)=$I$2), $A$2:$A$30)), 0)