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

 

[acarwreck]

Those two red comment are contradictory. You are either using Microsoft 365 or you are using Excel 2019. They are different products.

The final red statement above also contradicts your earlier assertion..

It also contradicts your user profile
View attachment 109513

What are you actually using? If you click File -> Account this is what I get.

View attachment 109512

What do you get?

this is what i get


EDIT:

after googling further
"The FILTER function has never been available in Excel in Office 2019. It was introduced in the subscription version Microsoft 365, and the first 'permanent license' version in which it is available is Office 2021."

Is there an alternative filter function for 2019 version?

 

[Peter_SSs]

"The FILTER function has never been available in Excel in Office 2019.

Exactly! I used the FILTER function because your profile showed Microsoft 365 and when I specifically asked, you confirmed that you were using 365. .. but you are not!

 

[acarwreck]

Exactly! I used the FILTER function because your profile showed Microsoft 365 and when I specifically asked, you confirmed that you were using 365. .. but you are not!

well **** myself and microsoft for this blunder, im sorry about i this. could you suggest an alternative?

 

[Peter_SSs]

Is there an alternative filter function for 2019 version?

2019 does not have a ready-made alternative to the later version FILTER function.

could you suggest an alternative?

You could try a user-defined vba function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across. Make the range for the price change data ($BA2:$BC100 in my formulas) plenty big enough to allow for any price changes that might get entered in the future.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
  
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 1
    Do
      If vPrices(j, 1) = vItems(i, 1) And vPrices(j, 2) <= dDayDate Then k = j
      j = j + 1
    Loop Until vPrices(j - 1, 2) > dDayDate Or j > UBound(vPrices)
    DayTot = DayTot + vQty(i, 1) * vPrices(k, 3)
  Next i
End Function

acarwreck.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 (2)

Cell Formulas
Range	Formula
H19:L19	H19	=DayTot($E14:$E18,H14:H18,H13,$BA2:$BC100)

 

[acarwreck]

2019 does not have a ready-made alternative to the later version FILTER function.


You could try a user-defined vba function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across. Make the range for the price change data ($BA2:$BC100 in my formulas) plenty big enough to allow for any price changes that might get entered in the future.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
 
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 1
    Do
      If vPrices(j, 1) = vItems(i, 1) And vPrices(j, 2) <= dDayDate Then k = j
      j = j + 1
    Loop Until vPrices(j - 1, 2) > dDayDate Or j > UBound(vPrices)
    DayTot = DayTot + vQty(i, 1) * vPrices(k, 3)
  Next i
End Function

acarwreck.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 (2)

Cell Formulas
Range	Formula
H19:L19	H19	=DayTot($E14:$E18,H14:H18,H13,$BA2:$BC100)

Hey this worked. thank you so much for your help. Have a great day

 

[Peter_SSs]

You're welcome. Thanks for letting us know the result.

 

[acarwreck]

2019 does not have a ready-made alternative to the later version FILTER function.


You could try a user-defined vba function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across. Make the range for the price change data ($BA2:$BC100 in my formulas) plenty big enough to allow for any price changes that might get entered in the future.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
 
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 1
    Do
      If vPrices(j, 1) = vItems(i, 1) And vPrices(j, 2) <= dDayDate Then k = j
      j = j + 1
    Loop Until vPrices(j - 1, 2) > dDayDate Or j > UBound(vPrices)
    DayTot = DayTot + vQty(i, 1) * vPrices(k, 3)
  Next i
End Function

acarwreck.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 (2)

Cell Formulas
Range	Formula
H19:L19	H19	=DayTot($E14:$E18,H14:H18,H13,$BA2:$BC100)

Register1
	D	E	F	G	H	I	J	K	L																																							AY	AZ	BA	BB	BC	BD	BE	BF	BG
1																																																	PRICES
2																																																	Item	Date	45292	45293	45294	45295	45296
3																																																	APPLE		50		55		57
4																																																	ORANGE		30
5																																																	POTATOES		54
6																																																	SPINACH		32	33
7																																																	EGGPLANT		12
8
9
10
11
12
13		SALES
14		ITEM		DATE :	45292	45293	45294	45295	45296
15		APPLE		QTY	5		5	5	1
16		ORANGE		QTY	32	3			1
17		POTATOES		QTY		4			1
18		SPINACH		QTY	52		4		1
19		EGGPLANT		QTY	5		2		1
20				DAY TOTAL	2934	306	402	250	178
21
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
H20:L20	H20	=DayTot($E15:$E19,H15:H19,H14,$AZ3:$BB101)

Hey i have made a small change here previously you had put the change of prices column wise, i want to put it row-wise as shown above, so could you make necessary changes to the formula that you have given?

 

[Peter_SSs]

Hey i have made a small change

Is this the last one?

Assuming the values in col AZ are in the identical order to the values in col E then try this.
Again make sure the range is big enough (mine goes to col CZ)

Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
  
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 3
    Do
      If vPrices(1, j) <= dDayDate And vPrices(i + 1, j) <> "" Then k = j
      j = j + 1
    Loop Until vPrices(1, j - 1) > dDayDate Or j > UBound(vPrices, 2)
    DayTot = DayTot + vQty(i, 1) * vPrices(i + 1, k)
  Next i
End Function

acarwreck.xlsm
	E	F	G	H	I	J	K	L	M																																							AZ	BA	BB	BC	BD	BE	BF	BG	BH
1																																																PRICES
2																																																Item	Date	45292	45293	45294	45295	45296
3																																																APPLE		50		55		57
4																																																ORANGE		30
5																																																POTATOES		54
6																																																SPINACH		32	33
7																																																EGGPLANT		12
8
9
10
11
12
13	SALES
14	ITEM		DATE :	45292	45293	45294	45295	45296
15	APPLE		QTY	5		5	5	1
16	ORANGE		QTY	32	3			1
17	POTATOES		QTY		4			1
18	SPINACH		QTY	52		4		1
19	EGGPLANT		QTY	5		2		1
20			DAY TOTAL	2934	306	431	275	186
Changing price (3)

Cell Formulas
Range	Formula
H20:L20	H20	=DayTot($E15:$E19,H15:H19,H14,$AZ2:$CZ7)

 

[acarwreck]

Is this the last one?

Assuming the values in col AZ are in the identical order to the values in col E then try this.
Again make sure the range is big enough (mine goes to col CZ)

Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
 
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 3
    Do
      If vPrices(1, j) <= dDayDate And vPrices(i + 1, j) <> "" Then k = j
      j = j + 1
    Loop Until vPrices(1, j - 1) > dDayDate Or j > UBound(vPrices, 2)
    DayTot = DayTot + vQty(i, 1) * vPrices(i + 1, k)
  Next i
End Function

acarwreck.xlsm
	E	F	G	H	I	J	K	L	M																																							AZ	BA	BB	BC	BD	BE	BF	BG	BH
1																																																PRICES
2																																																Item	Date	45292	45293	45294	45295	45296
3																																																APPLE		50		55		57
4																																																ORANGE		30
5																																																POTATOES		54
6																																																SPINACH		32	33
7																																																EGGPLANT		12
8
9
10
11
12
13	SALES
14	ITEM		DATE :	45292	45293	45294	45295	45296
15	APPLE		QTY	5		5	5	1
16	ORANGE		QTY	32	3			1
17	POTATOES		QTY		4			1
18	SPINACH		QTY	52		4		1
19	EGGPLANT		QTY	5		2		1
20			DAY TOTAL	2934	306	431	275	186
Changing price (3)

Cell Formulas
Range	Formula
H20:L20	H20	=DayTot($E15:$E19,H15:H19,H14,$AZ2:$CZ7)

HEY, thank you so much, i believe this has solved all my problems.

 

[Peter_SSs]

HEY, thank you so much,

You're welcome.

i believe this has solved all my problems.