Calculating Collection of sales Over Tme

[AllisterB]

I have Sales by Month and a profile of what % of each Amount will be collected in the month of sale, 1 month after sale etc.

What I need to do is calculate the total amount I will receive by month.

In the attached I need some way to calculate (on one row) the amounts io on theTotal Row.

I have tried Sumproduct but not working.

Any help would be appreciated.

Thank You

Allister

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
8		April	May			Months After Sales	0	1	3	6
9	Sales	$ 100.00	$ 200.00			Percentage of Amount Received	10%	20%	30%	40%
10
11
12		April	May	June	July	August	September	October	November		Total Received
13		100*0.1	100*0.2	100*0.3				100*0.4			$ 100.00
14			200*0.1	200*0.2	200*0.3				200*0.4		$ 200.00
15
16
17	Total	10	40	70	60			40	80		$ 300.00
Sheet1

Cell Formulas
Range	Formula
K17	K17	=SUM(B17:J17)

 

[Toadstool]

Hi AllisterB,

I've taken the liberty of restructuring your data a little to avoid too many lookups.
I've also added a Conditional Format to highlight when the collections will be outside of your heading date range.
I note you example was for values of 0,1,2,6 but you stated 0,1,3,6.

AllisterB.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1							Months After Sales	0	1	3	6
2							Percentage of Amount Received	0.1	0.2	0.3	0.4
3
4
5	Sale Date	Sales	April	May	June	July	August	September	October	November		Total Received
6	04-Apr-20	100	10	20		30			40			100
7	05-May-20	200		20	40		60			80		200
8	06-Jun-20	500			50	100		150				300
9
10	Total		10	40	90	130	60	150	40	80		600
Sheet1

Cell Formulas
Range	Formula
D5:J5	D5	=EOMONTH(C5,0)+1
C6:J8	C6	=IFERROR(INDEX($H$2:$K$2,MATCH(DATEDIF($A6,C$5+15,"M"),$H$1:$K$1,0))*$B6,"")
L10,L6:L8	L6	=SUM(C6:J6)
C10:J10	C10	=SUM(C6:C9)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L6:L16	Cell Value	<$B6	text	NO

 

[AllisterB]

Hi

Thanks for your reply.

I was looking for a formula that I could put on one row that wouold do what is currently done on rows 6 thru 10. The formulkla would say for May take 20% of 100 and 10% of 200 and provide teh total in a cell in column D. The result will be teh total of money received in May for sales made up to the end of may

Your reply provides a robust way of doing it and that is good. What I need is a formula that will do this on one row.

Thanks again

Allister

 

[Toadstool]

Allister,

OK, I've returned to your original format but with dates for the month names and just calculated the Totals.

AllisterB.xlsx
	A	B	C	D	E	F	G	H	I	J	K
8		April	May			Months After Sales	0	1	3	6
9	Sales	100	200			Percentage of Amount Received	0.1	0.2	0.3	0.4
10
11
12		April	May	June	July	August	September	October	November		Total Received
13		100*0.1	100*0.2	100*0.3				100*0.4			100
14			200*0.1	200*0.2	200*0.3				200*0.4		200
15
16
17	Total	10	40	40	30	60	0	40	80		300
Sheet2

Cell Formulas
Range	Formula
C12:I12	C12	=EOMONTH(B12,0)+1
B17:I17	B17	=IFERROR(INDEX($G$9:$J$9,MATCH(DATEDIF($B$8,B$12+15,"M"),$G$8:$J$8,0))*$B$9,0)+IFERROR(INDEX($G$9:$J$9,MATCH(DATEDIF($C$8,B$12+15,"M"),$G$8:$J$8,0))*$C$9,0)
K17	K17	=SUM(B17:J17)

 

[AllisterB]

Thank You