Abstracting the data

[Sunil Pinto]

I have the data of the date and cars moved on a particular road.
I need the maximum care moved in a certain period out of data.
Example: According to the below data my from date is 6/10/2024 to 26/10/2024 and I need a maximum of cars moved in 2 consecutive days
Can anybody help me to design the formula?

 

[Sunil Pinto]

@Sergius I think the dates in I6 & I7 are meant to be worked out, as the two dates that return the highest value.

Hello! Option using formula.

Book1.xlsm
	B	C	D	E	F	G	H	I
2	Dates	Cars				From	To	Days
3	01.10.2024	5				06.10.2024	26.10.2024	2
4	02.10.2024	2
5	03.10.2024	5					Answer	17
6	04.10.2024	7					From Date	10.10.2024
7	05.10.2024	6					To Date	11.10.2024
8	06.10.2024	1
9	07.10.2024	2
10	08.10.2024	5
11	09.10.2024	7
12	10.10.2024	8
13	11.10.2024	9
14	12.10.2024	5
15	13.10.2024	3
16	14.10.2024	2
17	15.10.2024	1
18	16.10.2024	4
19	17.10.2024	5
20	18.10.2024	6
21	19.10.2024	7
22	20.10.2024	4
23	21.10.2024	3
24	22.10.2024	2
25	23.10.2024	1
26	24.10.2024	6
27	25.10.2024	8
28	26.10.2024	4
29	27.10.2024	3
30	28.10.2024	5
31	29.10.2024	7
32	30.10.2024	9
Sheet11

Cell Formulas
Range	Formula
I5	I5	=SUMIFS(C3:C32,B3:B32,">="&I6,B3:B32,"<="&I7)
B4:B32	B4	=B3+1

Sir,
My inputs are G3,H3 and I3
I need output as I5, I6 and I7
Hope you understood

 

[Sunil Pinto]

Also,

here is a fairly easy way using pre-365 formulas. Just using 1 helper column.

EXCEL
	E	F	G	H	I	J	K	L	M
3	Dates	Cars	Helper			From	To	Days
4	10/1/2024	5	0			10/6/2024	10/26/2024	2
5	10/2/2024	2	0
6	10/3/2024	5	0					Answer	17
7	10/4/2024	7	0					From Date	10/10/2024
8	10/5/2024	6	0					To Date	10/11/2024
9	10/6/2024	1	3
10	10/7/2024	2	7
11	10/8/2024	5	12
12	10/9/2024	7	15
13	10/10/2024	8	17
14	10/11/2024	9	14
15	10/12/2024	5	8
16	10/13/2024	3	5
17	10/14/2024	2	3
18	10/15/2024	1	5
19	10/16/2024	4	9
20	10/17/2024	5	11
21	10/18/2024	6	13
22	10/19/2024	7	11
23	10/20/2024	4	7
24	10/21/2024	3	5
25	10/22/2024	2	3
26	10/23/2024	1	7
27	10/24/2024	6	14
28	10/25/2024	8	12
29	10/26/2024	4	7
30	10/27/2024	3	0
31	10/28/2024	5	0
32	10/29/2024	7	0
33	10/30/2024	9	0
Sheet10

Cell Formulas
Range	Formula
M6	M6	=MAX(G4:G33)
M7	M7	=INDEX(E4:E33,MATCH(M6,G4:G33,0))
M8	M8	=M7+L4-1
E5:E33	E5	=E4+1
G4:G33	G4	=SUM(F4:OFFSET(F4,$L$4-1,0))*(E4>=$J$4)*(E4<=$K$4)

Mr.Irobbo314,
Excellent, you solved my problem
It was completely up to my requirement
thanks a lot for your favor,
But still, I want to figure out how to use the VBA.

Thank you again.

 

[Sunil Pinto]

Sunil,

I can't see what your range references are, so I'm not sure if everything is coded correctly. Let me know if your data starts in B2, like I assumed in the post I made.

If that is correct, after you have pasted the code, go back to your worksheet, hit Alt+F8, then run the macro.

Sir,
Can you able to open this link and find out where I am wrong?

https://1drv.ms/x/c/4ac6cf5129cd6403/EdjHBM1qxlRCuQgtNasR7esB83dU6TY6hZ8WB_4mgMwBRQ?e=xUX1yt