if the prices keep changing, how do i add the different prices for different days is there any other way to fix this?

[acarwreck]

	PRICE	PRICE	Date:	1	2	3	4	5
APPLE	45	25	QTY	1	2	4		6
ORANGE	30	35	QTY		1		1
BRICK	100	125	QTY	2
MOON	150	140	QTY	4			7	2
FERRARI	25	500	QTY		3			5
	DAY TOTAL			845	195	180	1080	695

 

[Peter_SSs]

Your question is not at all clear. Please try to spell it out in more details and give not only sample data but the expected results and explain again in relation to that.

Also, when using XL2BB it is generally better to use Mini Sheet so we can see what columns/rows the data is in and if there are any formulas involved.

 

[acarwreck]

Your question is not at all clear. Please try to spell it out in more details and give not only sample data but the expected results and explain again in relation to that.

Also, when using XL2BB it is generally better to use Mini Sheet so we can see what columns/rows the data is in and if there are any formulas involved.

REGISTER APRIL.xlsx
	B	C	D	E	F	G	H	I	J	K
4
5
6		PRICE on 1st april	PRICE on 2nd april	Date:	1st APRIL	2ND APRIL	3	4	5
7	APPLE	45	25	QTY	1	2
8	ORANGE	30	35	QTY		1
9	BRICK	100	125	QTY	2
10	MOON	150	140	QTY	4
11	FERRARI	25	500	QTY		3
12
13		DAY TOTAL			845	195	0	0	0
14					expected value on 2nd april---->	1585
15
16
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
F13:J13	F13	=SUMPRODUCT($C$7:$C$11,F7:F11)

So sorry about the unclear question, so as you can see that on 1st and 2nd April there are different prices for those items and in order to calculate the total amount of each day i have used a formula thanks to this forum, now that particular formula uses one static value for the price of those items, now what if the price of certain items changes the next day? how do i get the total amount of each day if the prices of those items are changing everyday? i hope this is enough, please feel free to ask more questions.

 

[Peter_SSs]

What is your actual data like?
This ..

.. is different to this ..

.. and even that second image is inconsistent. Do the dates say "1st APRIL", "2ND APRIL" etc or do they just say "3", "4" etc

 

[RobOrBob]

Is the data manually input?
Use the worksheet_change event to trap the event e.g.
Private Sub Worksheet_Change(ByVal Target As Range)
...
If Not Intersect(Target, ws.Range("range to monitor")) Is Nothing Then
...

 

[acarwreck]

What is your actual data like?
This ..
View attachment 109242
.. is different to this ..

View attachment 109243

.. and even that second image is inconsistent. Do the dates say "1st APRIL", "2ND APRIL" etc or do they just say "3", "4" etc
View attachment 109245

What is your actual data like?
This ..
View attachment 109242
.. is different to this ..

View attachment 109243

.. and even that second image is inconsistent. Do the dates say "1st APRIL", "2ND APRIL" etc or do they just say "3", "4" etc
View attachment 109245

So sorry about the unclear question, so as you can see that on 1st and 2nd April there are different prices for those items and in order to calculate the total amount of each day i have used a formula thanks to this forum, now that particular formula uses one static value for the price of those items, now what if the price of certain items changes the next day? how do i get the total amount of each day if the prices of those items are changing everyday? i hope this is enough, please feel free to ask more questions.

REGISTER APRIL.xlsx
	E	F	G	H	I	J	K	L	M
11
12
13	ITEM	PRICE	DATE :	1	2	3	4	5
14	APPLE	50	QTY	5
15	ORANGE	30	QTY	32
16	POTATOES	54	QTY
17	SPINACH	32	QTY	52
18	EGGPLANT	12	QTY	5
19			DAY TOTAL	2934	0	0	0	0
20
21
Sheet3

Cell Formulas
Range	Formula
H19:L19	H19	=SUMPRODUCT($F$14:$F$18,H14:H18)

This is an example of what my table initially looked like. i have selected first 5 days of the month. Now the prices of some items change on the next day sometimes how do i modify this table in such a way that i can update the prices some items everyday? maybe tomorrow the cost of apples might increase the rest might remain the same. here is an example:

REGISTER APRIL.xlsx
	D	E	F	G	H	I	J	K	L	M	N
11
12
13		ITEM	PRICE	UPDATED PRICE	DATE :	1	2	3	4	5
14		APPLE	50	60	QTY	5	5
15		ORANGE	30		QTY	32	32
16		POTATOES	54		QTY
17		SPINACH	32		QTY	52	52
18		EGGPLANT	12		QTY	5	5
19					DAY TOTAL	2934	300	0	0	0
20
21
22
Sheet3

Cell Formulas
Range	Formula
I19,K19:M19	I19	=SUMPRODUCT($F$14:$F$18,I14:I18)
J19	J19	=SUMPRODUCT($G$14:$G$18,J14:J18)

the price of the apple increased while the rest remained the same. how to calculate the day total with the updated apple price and old prices of the other items?

 

[acarwreck]

Is the data manually input?
Use the worksheet_change event to trap the event e.g.
Private Sub Worksheet_Change(ByVal Target As Range)
...
If Not Intersect(Target, ws.Range("range to monitor")) Is Nothing Then
...

could you elaborate this with an example if you dont mind?

 

[Peter_SSs]

1. With your sample in post #6, how would we know that apples increased on the 2nd of the month and not on the 3rd?
2. If apples increased again on the 4th of the month to 65, where would the information be that the price on the 1st was 50, price on the 2nd was 60 and price on the 4th was 65?

 

[acarwreck]

Yes that's one of my problems here as well. the prices keep changing sometimes it doesn't. i don't know how to store it. if you could suggest that as well, would be nice.

 

[Peter_SSs]

if you could suggest that as well, would be nice.

Could you use something like this?

Make a table like I have in columns BA:BC. This table records the date that each item changes price and what that ne price is. This column could even be on a different worksheet.
Notice that the values in the bright blue cells in row 13 are actually dates but I have formatted them to just show the day.
The formulas in row19 look up the latest price as at the date in row 13 and multiply by the quantities in rows 14:18

24 04 02.xlsm
	E	F	G	H	I	J	K	L	M																																							AZ	BA	BB	BC
1																																																	Item	Date	Price
2																																																	APPLE	1/01/2024	50
3																																																	ORANGE	1/01/2024	30
4																																																	POTATOES	1/01/2024	54
5																																																	SPINACH	1/01/2024	32
6																																																	EGGPLANT	1/01/2024	12
7																																																	SPINACH	2/01/2024	33
8																																																	APPLE	3/01/2024	55
9																																																	APPLE	5/01/2024	57
10
11
12
13	ITEM		DATE :	1	2	3	4	5
14	APPLE		QTY	5		5	5	1
15	ORANGE		QTY	32	3			1
16	POTATOES		QTY		4			1
17	SPINACH		QTY	52		4		1
18	EGGPLANT		QTY	5		2		1
19			DAY TOTAL	2934	306	431	275	186
20
Changing price

Cell Formulas
Range	Formula
H19:L19	H19	=LET(BA,$BA2:$BA1000,BB,$BB2:$BB1000,SUMPRODUCT(H14:H18,VLOOKUP($E14:$E18,FILTER($BA2:$BC1000,BB=MAXIFS(BB,BB,"<="&H13,BA,BA)),3,0)))