Get value/number within the dates

[wryud]

Hi All,

I have difficulties in finding the formula to get amount of some package used within date period. The idea of formula should be Purchase/Qty packages * date package used (The examples is in mini-sheet attached)

It takes me hours but i still not know how to manage it.

Can someone help me with the formula?

Thank you in advance

Yudi

Sales Report PP 2014-2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Question Froum
2
3	Name	Purchase	Qty Package	Date Package Used (dd:mm:yyyy)										Qty Left	Unit Price	Debt Amount Left
4				1x	2x	3x	4x	5x	6x	7x	8x	9x	10x
5	James	1.000	5	01/09/2020	30/09/2020	31/10/2020	30/11/2020	31/12/2020						0	200	0
6	Andri	5.000	10	05/06/2016	07/09/2016	08/03/2017	21/06/2017	28/10/2017	EXPIRED	EXPIRED	EXPIRED	EXPIRED	EXPIRED	0	500	0
7	Vina	8.000	8	02/02/2020	23/02/2020	08/08/2020	09/09/2020	15/10/2020	12/01/2021	19/03/2021				2	1000	2000
8	Total	14.000														2.000
9
10	QUESTION:
11	How to get package usage value with date range, with conditions:
12	Usage of range package (dd:mm:yyyy) 1/1/2016 - 31/12/2020 + Expired
13	The idea of formula should be = Purchase/Qty packages * date package used
14	The result should be:		11.000
15	Debt amount left:		3.000
16			14.000
17
Sheet2

Cell Formulas
Range	Formula
O5:O7	O5	=+B5/C5
B8	B8	=SUM(B5:B7)
P5:P7	P5	=+N5*O5
P8	P8	=+SUM(P5:P7)
C14	C14	=1000+5000+5000
C16	C16	=SUM(C14:C15)

 

[Peter_SSs]

Welcome to the MrExcel board!

A number of people have looked at you question but not answered, so I'm guessing that they are like me and not really understanding the problem.

In your sample
The result should be: =1000+5000+5000
Can you explain how/where you got those three values to add from the sample data?

Debt amount left: 3000
Can you explain how/where you got that value from the sample data?

 

[wryud]

Thank you for your answer,

I hope its not because my bad english

So i will explain further:
The result should be 11000, is from the date package used. Below is my explanation:
From the mini-sheet we can see in column B3 Purchase which have datas:
- James for 1000 with Qty 5 times, so we can know 200 is unit price. In row D5:H5 is filled with date. It means, all the packages has been used (because Qty is 5), then we can know outsanding debt is 0 (zero). 0 (zero) is from Purchase minus total (Qty_used*unit price).

- Andri for 5000 with Qty 10 times, so we can know 500 is unit price. In row D5:M5 is filled with date and "Expired" text. It means, the package has been used 5 times, and the "Expired" text means the package is already Expired. So it will count all Qty of package already used. Since the all qty package is used, we can know the outstanding debt is 0 (zero). 0 (zero) is from Purchase minus total (Qty_used*unit price).

- Vina for 8000 with Qty 8 times, so we can know 1000 is unit price. In row D7:J7 is filled with date. It means, the packages has been used for 7 times, with 1 times is still hanging. Then we can know outstanding debt is 1000. 1000 is from Purchase minus total (Qty_used*unit price).
Im sorry it was supposed to be Qty left 1 for 1000 not 2000 (My bad)

So we can summary:
- Total purchase is 14000
- Total package used is: 13000 (date between 2016 and 2021)
- Total package remaining is: 1000

So is it possible to make a formula to know the package used within periode for example 2016 to 2020?
If we can, it should be:
- James is 1000
- Andri is 5000
- Vina is 5000
So total package used will be 11000, and 3000 is remain debt.

I hope you understand my explanation