Daily datewise calculation with colour formatting

[vishu]

Hi

I have daily vegetable list want to calculate date wise formula. e.g Date column is A where fist cell is date rest is blank till my next date.

B is item C is qty D is rate E is Amount and Column F is where i want help which is Daily total formula need automatically till my next date. I tried =IF(AND(A3<>"",SUM(E$1:E2)),"") but result not giving me proper answer. Please help

A	B	C	D	E	F
Date	Item	Qty	Rate	Amt	Daily Total
01-Apr-24	Coriander	6	15	90
	Curry Leaves	3	6	18
	Green Onion	3	16	48
	Lemon	70	5	350
	Tomato	5	15	75
	Beans	2	60	120
	Gajar	3	40	120
	Cauliflower	5	30	150
	Capsicum	1	60	60
	Mirchi	2	70	140
	Kakadi	2	25	50
	Dudhi	2	30	60
	Shevga	1	50	50
	Bhopla	1.5	30	45
	PineApple	1	100	100
	Papaya	1	40	40
	Gobi	6	20	120	1636
02-Apr-24	Coriander	10	15	150
	Palak	5	10	50
	Pudina	5	6	30
	Curry Leaves	3	6	18
	Green Onion	2	15	30
	Lemon	80	5	400
	Tomato	10	16	160
	Ginger	1	120	120
	Garlic	1	180	180
	Beans	2	65	130
	Gajar	3	40	120
	Gobi	5	20	100
	Capsicum	1	60	60
	Kakadi	2	20	40	1588
03-Apr-24	Potato	5	32	160

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H
1	Date	Item	Qty	Rate	Amt	Daily Total
2	2024-04-01	Coriander	6	15	90
3		Curry Leaves	3	6	18
4		Green Onion	3	16	48
5		Lemon	70	5	350
6		Tomato	5	15	75
7		Beans	2	60	120
8		Gajar	3	40	120
9		Cauliflower	5	30	150
10		Capsicum	1	60	60
11		Mirchi	2	70	140
12		Kakadi	2	25	50
13		Dudhi	2	30	60
14		Shevga	1	50	50
15		Bhopla	2	30	45
16		PineApple	1	100	100
17		Papaya	1	40	40
18		Gobi	6	20	120	1636	1636
19	2024-04-02	Coriander	10	15	150
20		Palak	5	10	50
21		Pudina	5	6	30
22		Curry Leaves	3	6	18
23		Green Onion	2	15	30
24		Lemon	80	5	400
25		Tomato	10	16	160
26		Ginger	1	120	120
27		Garlic	1	180	180
28		Beans	2	65	130
29		Gajar	3	40	120
30		Gobi	5	20	100
31		Capsicum	1	60	60
32		Kakadi	2	20	40	1588	1588
Sheet1

Cell Formulas
Range	Formula
G2:G32	G2	=IF(ISNUMBER(A3),SUM(INDEX($E$2:E$500,MATCH(MAX($A$2:A2),$A$2:$A$500,0),1):E2),"")

 

[Cubist]

-revising-

 

[awoohaw]

@Cubist , i'm not sure they want a running total. But I like the simplicity of your formula. Is there a way to make it only go to the previous date?

 

[awoohaw]

deleted.

 

[vishu]

try this:

Book1
	A	B	C	D	E	F	G	H
1	Date	Item	Qty	Rate	Amt	Daily Total
2	2024-04-01	Coriander	6	15	90
3		Curry Leaves	3	6	18
4		Green Onion	3	16	48
5		Lemon	70	5	350
6		Tomato	5	15	75
7		Beans	2	60	120
8		Gajar	3	40	120
9		Cauliflower	5	30	150
10		Capsicum	1	60	60
11		Mirchi	2	70	140
12		Kakadi	2	25	50
13		Dudhi	2	30	60
14		Shevga	1	50	50
15		Bhopla	2	30	45
16		PineApple	1	100	100
17		Papaya	1	40	40
18		Gobi	6	20	120	1636	1636
19	2024-04-02	Coriander	10	15	150
20		Palak	5	10	50
21		Pudina	5	6	30
22		Curry Leaves	3	6	18
23		Green Onion	2	15	30
24		Lemon	80	5	400
25		Tomato	10	16	160
26		Ginger	1	120	120
27		Garlic	1	180	180
28		Beans	2	65	130
29		Gajar	3	40	120
30		Gobi	5	20	100
31		Capsicum	1	60	60
32		Kakadi	2	20	40	1588	1588
Sheet1

Cell Formulas
Range	Formula
G2:G32	G2	=IF(ISNUMBER(A3),SUM(INDEX($E$2:E$500,MATCH(MAX($A$2:A2),$A$2:$A$500,0),1):E2),"")

Sir thank you very very much. You saved me

 

[awoohaw]

Sir thank you very very much. You saved me

you're welcome, you can change the row 500 in the formula to the row you are on. I don't know what I was thinking when I thought that was needed. So in the cell F2 use this formula and drag it down:

=IF(ISNUMBER(A3),SUM(INDEX($E$2:$E2,MATCH(MAX($A$2:$A2),$A$2:$A2,0),1):$E2),"")

 

[Cubist]

@Cubist , i'm not sure they want a running total. But I like the simplicity of your formula. Is there a way to make it only go to the previous date?

I realized that afterwards. I've removed my post.