Separate the quantity in different columns basis date condition

[AtoZexport]

Dear Members,

I am facing a challenge as below where I have multiple dated stock of the same products whereas output report required the sum of Short, medium and long dated stock of each product.

To make it easy below is the sample of my Data and required output report.

Data:

Product Name	Quantity	Best Before Date
Mango Juice	10	22/06/2023
Mango Juice	20	20/09/2023
Mango Juice	30	17/02/2024
Mango Juice	20	18/03/2024
Orange Juice	40	22/03/2023
Orange Juice	60	18/09/2023
Orange Juice	80	16/01/2024

Output Report required:

Product Name	Short Date (If rem. days are less than 90)	Medium Date (If rem. of days >90<150)	Long Date (If rem. days >150)
Mango Juice	10	20	50
Orange Juice	40	60	80

Thank you in advance for your time and solution.

 

[kweaver]

How about:

051923 Sum Spreadsheet.xlsm
	F	G	H	I
1		Short	Medium	Long
2	Mango Juice	10	20	50
3	Orange Juice	40	60	80
Sheet7

Cell Formulas
Range	Formula
G2:G3	G2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()<90)))
H2:H3	H2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()>=90)*($C$2:$C$8-TODAY()<150)))
I2:I3	I2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()>=150)))

 

[AtoZexport]

How about:

051923 Sum Spreadsheet.xlsm
	F	G	H	I
1		Short	Medium	Long
2	Mango Juice	10	20	50
3	Orange Juice	40	60	80
Sheet7

Cell Formulas
Range	Formula
G2:G3	G2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()<90)))
H2:H3	H2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()>=90)*($C$2:$C$8-TODAY()<150)))
I2:I3	I2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()>=150)))

T

How about:

051923 Sum Spreadsheet.xlsm
	F	G	H	I
1		Short	Medium	Long
2	Mango Juice	10	20	50
3	Orange Juice	40	60	80
Sheet7

Cell Formulas
Range	Formula
G2:G3	G2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()<90)))
H2:H3	H2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()>=90)*($C$2:$C$8-TODAY()<150)))
I2:I3	I2	=SUM(FILTER($B$2:$B$8,($A$2:$A$8=$F2)*($C$2:$C$8-TODAY()>=150)))

Thank you.