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

 

[etaf]

would
=countif( Range with the values in, "<="&-1000)

or is this only where consecutive rows have less than 1000

if its the consecutive values
mayb e
=MAX(FREQUENCY(IF(B2:B10<-1000,ROW(B2:B10)),IF(B2:B10>=-1000,ROW(B2:B10))))

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[etaf]

Excel : How to Count Consecutive Positive / Negative Numbers

Multiple ways to calculate the number of consecutive positive and negative numbers in Excel

www.listendata.com

Book2
	A	B	C	D
1
2		-1001		3
3		-1001
4		2
5		3
6		-1001
7		-1001
8		-1001
9		7
10		14
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MAX(FREQUENCY(IF(B2:B10<-1000,ROW(B2:B10)),IF(B2:B10>=-1000,ROW(B2:B10))))

 

[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

 

[etaf]

not following sorry -
you want the sum of anything less than 0 for a particular year

you image says
£323 for 2023
i see -400

so not sure why its 323 in you answer

BUT it would be a
=SUMIFS( C2:C1000, A2:A1000, ">="&DATEVALUE("1/1/"&G2), A2:A1000,"<="&DATEVALUE("31/12/"&G2), C2:C1000, "<"&0)

you could change to <=0 , but if it =0 , then wont change the result
did you want to use G3 in the formula -
=SUMIFS( C2:C1000, A2:A1000, ">="&DATEVALUE("1/1/"&G2), A2:A1000,"<="&DATEVALUE("31/12/"&G2), C2:C1000, G3)

Book2
	A	B	C	D	E	F	G	H	I
1
2	12/1/23		-1001	3			2023
3	12/2/23		-1001				<=0		-5005
4	12/3/23		2
5	12/4/23		3
6	12/5/23		-1001
7	12/6/23		-1001
8	12/7/23		-1001
9			7
10			14
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MAX(FREQUENCY(IF(C2:C10<-1000,ROW(C2:C10)),IF(C2:C10>=-1000,ROW(C2:C10))))
I3	I3	=SUMIFS( C2:C1000, A2:A1000, ">="&DATEVALUE("1/1/"&G2), A2:A1000,"<="&DATEVALUE("31/12/"&G2), C2:C1000, G3)

 

[PAOLO7673]

not following sorry -
you want the sum of anything less than 0 for a particular year

you image says
£323 for 2023
i see -400

so not sure why its 323 in you answer

BUT it would be a
=SUMIFS( C2:C1000, A2:A1000, ">="&DATEVALUE("1/1/"&G2), A2:A1000,"<="&DATEVALUE("31/12/"&G2), C2:C1000, "<"&0)

you could change to <=0 , but if it =0 , then wont change the result
did you want to use G3 in the formula -
=SUMIFS( C2:C1000, A2:A1000, ">="&DATEVALUE("1/1/"&G2), A2:A1000,"<="&DATEVALUE("31/12/"&G2), C2:C1000, G3)

Book2
	A	B	C	D	E	F	G	H	I
1
2	12/1/23		-1001	3			2023
3	12/2/23		-1001				<=0		-5005
4	12/3/23		2
5	12/4/23		3
6	12/5/23		-1001
7	12/6/23		-1001
8	12/7/23		-1001
9			7
10			14
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MAX(FREQUENCY(IF(C2:C10<-1000,ROW(C2:C10)),IF(C2:C10>=-1000,ROW(C2:C10))))
I3	I3	=SUMIFS( C2:C1000, A2:A1000, ">="&DATEVALUE("1/1/"&G2), A2:A1000,"<="&DATEVALUE("31/12/"&G2), C2:C1000, G3)

I need the number of days (from first day till last day) the balance remained below zero or below a specific amount.
The 323 I mentioned is the total number of days from 11/01/2023 till 30/11/2023
Thanks for you fast reply!!

 

[etaf]

thanks for the clarification
a little more complicated then
in the example

you have 2 - where it shows 400 on the 1/1/22
then -200 on the 3/1/22 - WHY 2 days ???

i think possibly need quite a few more examples of the permutations that may be needed

i may not be able to help here , i maybe missing the obvious and cannot see it - ...... looks like a MIN and a MAX dates BUT not following the example,

when is the start date and why , when is the end date and why - then we can get the MIN and MAX and take them away to get days

but as posted - rather than image use xl2bb or a share

Book2
	A	B	C	D	E
1
2	1/1/23		100
3	2/2/23		0	or is this the start
4	3/23/23		-100	Start count of days -	3/3/23
5	4/4/23		0
6	5/4/23		0
7	5/10/23		200
8	6/11/23		0
9	9/1/23		-200
10	10/2/23		0
11	10/3/23		-100	last count of days	10/3/23
12	11/4/23		200
13	12/4/23		200
14
Sheet1

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Sam_D_Ben]

May be something like this, Input year in [$G$6]

Book3
	A	B	C	D	E	F	G	H
1	date	amount in/out	balance	days with negative balance				result
2	01/01/22	50	400			year	2023	323
3	01/03/22	-600	-200	2		balance limit	<=0
4	01/04/22	400	200
5	01/04/22	500	700
6	01/05/22	600	1300			year	2022	result
7	01/06/22	-1.4	-100	1		Days @ <0	<0	2
8	01/07/22	250	150
9	01/08/22	50	200
10	01/09/22	100	300
11	01/09/22	50	350
12	01/11/23	-500	-150
13	02/02/23	100	-50
14	02/05/23	50	0
15	06/06/23	-200	-200
16	06/01/23	200	0
17	11/30/23	100	100	323
18	11/30/23	200	300
19	11/30/23	350	650
20	11/30/23	600	1250
21	12/31/23	-1	250
22	01/01/24	-500	-250
23	01/02/24	600	350	1
24	01/03/24	300	650
25	01/03/24	250	900
26	01/03/24	-2	-1100
27	12/01/24	150	-950
28	12/01/24	100	-850
29	12/31/24	50	-800
30	01/01/25	800	0	364
Sheet3

Cell Formulas
Range	Formula
H7	H7	=SUM(--(FREQUENCY(IF((YEAR(A2:A30)=$G$6)*(C2:C30<0), MATCH(A2:A30, A2:A30, 0)), ROW(A2:A30)-ROW(A2)+1)>0))

 

[PAOLO7673]

BANCA 22-24.xlsx
	A	B	C	D	E	F	G	H
1	date	amount in/out	balance	days with negative balance				result
2	01/01/2022	50	400			year	2023	146
3	03/01/2022	- 600	-200	2		balance limit	<=0
4	04/01/2022	400	200
5	04/01/2022	500	700
6	05/01/2022	600	1300
7	06/01/2022	- 1.400	-100	1	3
8	07/01/2022	250	150
9	08/01/2022	50	200
10	09/01/2022	100	300
11	09/01/2022	50	350
12	11/01/2023	- 500	-150
13	02/02/2023	100	-50
14	05/02/2023	50	0
15	01/06/2023	- 200	-200
16	06/06/2023	200	0	146	146
17	30/11/2023	100	100
18	30/11/2023	200	300
19	30/11/2023	350	650
20	30/11/2023	600	1250
21	31/12/2023	- 1.000	250
22	01/01/2024	- 500	-250	1
23	02/01/2024	600	350
24	03/01/2024	300	650
25	03/01/2024	250	900
26	03/01/2024	- 2.000	-1100
27	01/12/2024	150	-950
28	01/12/2024	100	-850
29	31/12/2024	50	-800
30	31/12/2024	800	0	363	364
Foglio1

Cell Formulas
Range	Formula
D3,D7	D3	=A3-A2
E7	E7	=+D3+D7
D16	D16	=SUM(A16-A12)
E16	E16	=+D16
C3:C30	C3	=+C2+B3
D30	D30	=A30-A26
E30	E30	=+D30+D22

Sorry, not getting the desired amount. Here is a coloured version that should make the calculation. For 2023 the TOTAL days with <=0 balance were 146 . Many thanks

 

[Sam_D_Ben]

@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)