Dynamic way of calculating billing values between two months

[Akhalkalak]

Hi all,

I have a data set that contains clients' names, client ID, product ID, annual amount billed, client industry, and product purchase date (also the start date of billing). I'm trying to find a way to input any customer name and any two months (February - November, April-May, etc) and retrieve the total amount billed to said client in this time frame.

Customers vary by industry and each industry has a different billing frequency. I've used IFS to calculate the next billing period for every customer based on their industry and that industry's billing frequency. The purchases & billing all begins in 2014/15, and I am trying to find the amount billed between any two months in 2017 (all purchases are renewed every billing period).

I am a bit confused on how to approach the rest of this project from here on out and would really appreciate some feedback. Since there is a set amount billed annually, wouldn't it make sense that it doesn't really matter what specific day billing began for a customer? If a customer is billed quarterly, and say it began on 3/13/2014, how would I get to the 2017 values that would allow me to input any two months and customer name and get the correct $$$ billed?

 

[Akhalkalak]

here is an example of the data

 

[AhoyNC]

Maybe something like this:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Customer Name	Product ID	Customer ID	Industry	Amount Billed Annual	Product Purchase Date		Customer	Start Month	End Month	Year
2	Betasoloin	fdw115rt	OLX3928	Commercial Property Mgmt	31,669	6/8/2015		PSF4581	3	9	2015
3	Betasoloin	qbdt1041g	OLX3928	Commercial Property Mgmt $	8,756	5/15/2014		Amount
4	Betasoloin	auuh99kd	OLX3928	Commercial Property Mgmt $	43,267	7/11/2014		$ 102,932.00
5	Betasoloin	htoi103fe	OLX3928	Commercial Property Mgmt $	71,194	3/19/2014
6	Betatech	pmad110jy	ENC2036	Higher Education	61,112	3/24/2014
7	Betatech	nhon112tr	ENC2036	Higher Education	45,694	5/7/2014
8	Betatech	ahws101jq	ENC2036	Higher Education	52,449	4/20/2014
9	Betatech	bod117za	ENC2036	Higher Education	7,294	1/25/2014
10	Bioholding	bqkm112wm	PSF4581	Corporate	37,116	2/19/2015
11	Bioholding	chot112re	PSF4581	Corporate	62,209	11/25/2014
12	Bioholding	cwla115sb	PSF4581	Corporate	37,429	6/7/2015
13	Bioholding	cljx118ai	PSF4581	Corporate	65,503	9/28/2015
14	Bioplex	niri120ia	RUH5273	Higher Education	49,434	12/24/2015
15	Bioplex	yjuu121et	RUH5273	Higher Education	4,515	7/9/2014
16	Bioplex	frh1113zm	RUH5273	Higher Education	39,854	10/13/2014
17	Bioplex	xydvl 16tu	RUH5273	Higher Education	74,789	11/10/2015
18	Bioplex	a btb98wm	RUH5273	Higher Education	81,724	10/8/2015
19	Bioplex	xcgz111se	RUH5273	Higher Education	4,121	7/28/2015
20	Bioplex	orug114ew	RUH5273	Higher Education	73,574	2/25/2014
21	Bioplex	mwavl17zn	RUH5273	Higher Education	19,514	2/7/2014
22	Blackzim	quwa109rnw	MZV8706	Retail	52,007	4/26/2015
23	Blackzim	coqd112fm	MZV8706	Retail	78,365	2/24/2014
24	Blackzim	wuzql 17ke	MZV8706	Retail	97,284	4/2/2015
25	Blackzim	tdds108zh	MZV8706	Retail	77,778	5/23/2015
26	Blackzim	hgdm108kv	MZV8706	Retail	21,270	2/2/2014
27	Blackzim	astc99xy	MZV8706	Retail	60,753	7/16/2014
28	Cancity	hsab119cq	WR15364	Higher Education	94,123	9/25/2014
29	Cancity	vde1116re	WR15364	Higher Education	89,894	11/12/2015
30	Cancity	jwqz98zk	WR15364	Higher Education	23,473	12/10/2015
31	Cancity	yxwb109nq	WR15364	Higher Education	64,573	12/26/2014
Sheet1

Cell Formulas
Range	Formula
H4	H4	=SUM(FILTER($E$2:$E$31,($C$2:$C$31=$H$2)*(MONTH($F$2:$F$31)>=$I$2)*(MONTH($F$2:$F$31)<=$J$2)*(YEAR($F$2:$F$31=$K$2))))

 

[Akhalkalak]

Maybe something like this:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Customer Name	Product ID	Customer ID	Industry	Amount Billed Annual	Product Purchase Date		Customer	Start Month	End Month	Year
2	Betasoloin	fdw115rt	OLX3928	Commercial Property Mgmt	31,669	6/8/2015		PSF4581	3	9	2015
3	Betasoloin	qbdt1041g	OLX3928	Commercial Property Mgmt $	8,756	5/15/2014		Amount
4	Betasoloin	auuh99kd	OLX3928	Commercial Property Mgmt $	43,267	7/11/2014		$ 102,932.00
5	Betasoloin	htoi103fe	OLX3928	Commercial Property Mgmt $	71,194	3/19/2014
6	Betatech	pmad110jy	ENC2036	Higher Education	61,112	3/24/2014
7	Betatech	nhon112tr	ENC2036	Higher Education	45,694	5/7/2014
8	Betatech	ahws101jq	ENC2036	Higher Education	52,449	4/20/2014
9	Betatech	bod117za	ENC2036	Higher Education	7,294	1/25/2014
10	Bioholding	bqkm112wm	PSF4581	Corporate	37,116	2/19/2015
11	Bioholding	chot112re	PSF4581	Corporate	62,209	11/25/2014
12	Bioholding	cwla115sb	PSF4581	Corporate	37,429	6/7/2015
13	Bioholding	cljx118ai	PSF4581	Corporate	65,503	9/28/2015
14	Bioplex	niri120ia	RUH5273	Higher Education	49,434	12/24/2015
15	Bioplex	yjuu121et	RUH5273	Higher Education	4,515	7/9/2014
16	Bioplex	frh1113zm	RUH5273	Higher Education	39,854	10/13/2014
17	Bioplex	xydvl 16tu	RUH5273	Higher Education	74,789	11/10/2015
18	Bioplex	a btb98wm	RUH5273	Higher Education	81,724	10/8/2015
19	Bioplex	xcgz111se	RUH5273	Higher Education	4,121	7/28/2015
20	Bioplex	orug114ew	RUH5273	Higher Education	73,574	2/25/2014
21	Bioplex	mwavl17zn	RUH5273	Higher Education	19,514	2/7/2014
22	Blackzim	quwa109rnw	MZV8706	Retail	52,007	4/26/2015
23	Blackzim	coqd112fm	MZV8706	Retail	78,365	2/24/2014
24	Blackzim	wuzql 17ke	MZV8706	Retail	97,284	4/2/2015
25	Blackzim	tdds108zh	MZV8706	Retail	77,778	5/23/2015
26	Blackzim	hgdm108kv	MZV8706	Retail	21,270	2/2/2014
27	Blackzim	astc99xy	MZV8706	Retail	60,753	7/16/2014
28	Cancity	hsab119cq	WR15364	Higher Education	94,123	9/25/2014
29	Cancity	vde1116re	WR15364	Higher Education	89,894	11/12/2015
30	Cancity	jwqz98zk	WR15364	Higher Education	23,473	12/10/2015
31	Cancity	yxwb109nq	WR15364	Higher Education	64,573	12/26/2014
Sheet1

Cell Formulas
Range	Formula
H4	H4	=SUM(FILTER($E$2:$E$31,($C$2:$C$31=$H$2)*(MONTH($F$2:$F$31)>=$I$2)*(MONTH($F$2:$F$31)<=$J$2)*(YEAR($F$2:$F$31=$K$2))))

Sorry, here is info on the billing periods as well, left out an important piece

 

[AhoyNC]

So, what are your expected results?

 

[Akhalkalak]

So, what are your expected results?

Well, I first need to get 2017 billing. There is a 100% renewal rate assumption, so I see how you set up the way to find the total, but wouldn't I have to go into 2017 and factor in the billing frequency based on industry as well? I am just confused on how to approach this entire thing

 

[Akhalkalak]

So, what are your expected results?

Also, how would I convert

Maybe something like this:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Customer Name	Product ID	Customer ID	Industry	Amount Billed Annual	Product Purchase Date		Customer	Start Month	End Month	Year
2	Betasoloin	fdw115rt	OLX3928	Commercial Property Mgmt	31,669	6/8/2015		PSF4581	3	9	2015
3	Betasoloin	qbdt1041g	OLX3928	Commercial Property Mgmt $	8,756	5/15/2014		Amount
4	Betasoloin	auuh99kd	OLX3928	Commercial Property Mgmt $	43,267	7/11/2014		$ 102,932.00
5	Betasoloin	htoi103fe	OLX3928	Commercial Property Mgmt $	71,194	3/19/2014
6	Betatech	pmad110jy	ENC2036	Higher Education	61,112	3/24/2014
7	Betatech	nhon112tr	ENC2036	Higher Education	45,694	5/7/2014
8	Betatech	ahws101jq	ENC2036	Higher Education	52,449	4/20/2014
9	Betatech	bod117za	ENC2036	Higher Education	7,294	1/25/2014
10	Bioholding	bqkm112wm	PSF4581	Corporate	37,116	2/19/2015
11	Bioholding	chot112re	PSF4581	Corporate	62,209	11/25/2014
12	Bioholding	cwla115sb	PSF4581	Corporate	37,429	6/7/2015
13	Bioholding	cljx118ai	PSF4581	Corporate	65,503	9/28/2015
14	Bioplex	niri120ia	RUH5273	Higher Education	49,434	12/24/2015
15	Bioplex	yjuu121et	RUH5273	Higher Education	4,515	7/9/2014
16	Bioplex	frh1113zm	RUH5273	Higher Education	39,854	10/13/2014
17	Bioplex	xydvl 16tu	RUH5273	Higher Education	74,789	11/10/2015
18	Bioplex	a btb98wm	RUH5273	Higher Education	81,724	10/8/2015
19	Bioplex	xcgz111se	RUH5273	Higher Education	4,121	7/28/2015
20	Bioplex	orug114ew	RUH5273	Higher Education	73,574	2/25/2014
21	Bioplex	mwavl17zn	RUH5273	Higher Education	19,514	2/7/2014
22	Blackzim	quwa109rnw	MZV8706	Retail	52,007	4/26/2015
23	Blackzim	coqd112fm	MZV8706	Retail	78,365	2/24/2014
24	Blackzim	wuzql 17ke	MZV8706	Retail	97,284	4/2/2015
25	Blackzim	tdds108zh	MZV8706	Retail	77,778	5/23/2015
26	Blackzim	hgdm108kv	MZV8706	Retail	21,270	2/2/2014
27	Blackzim	astc99xy	MZV8706	Retail	60,753	7/16/2014
28	Cancity	hsab119cq	WR15364	Higher Education	94,123	9/25/2014
29	Cancity	vde1116re	WR15364	Higher Education	89,894	11/12/2015
30	Cancity	jwqz98zk	WR15364	Higher Education	23,473	12/10/2015
31	Cancity	yxwb109nq	WR15364	Higher Education	64,573	12/26/2014
Sheet1

Cell Formulas
Range	Formula
H4	H4	=SUM(FILTER($E$2:$E$31,($C$2:$C$31=$H$2)*(MONTH($F$2:$F$31)>=$I$2)*(MONTH($F$2:$F$31)<=$J$2)*(YEAR($F$2:$F$31=$K$2))))

This also does not account for the billing periods, so when I enter a customer name, it just gives the annual amount

 

[AhoyNC]

So, if we take your customer Bioplex what answer do you want to see?

 

[Akhalkalak]

So, if we take your customer Bioplex what answer do you want to see?

I want to be able to see the total $ amount that they will be billed based on any two month period in 2017. For example, if I input 2 for February and 7 for July, I want to see how much $$$ they will be billed. Every customer is only in one industry so there is one billing period per customer regardless of the different products they have purchased. I don't know how to factor that into this and I can't seem to be able to create a data table either.

 

[AhoyNC]

If we take the customer Bioplex for the months Feb & July.
They have 2 records each for Feb. & July which add up to $101,724. Is this the answer you want or would it be something else?
If this is not the correct answer, then what should it be (actual dollar amount) and how did you get it?

Book1
	A	B	C	D	E	F
1	Customer Name	Product ID	Customer ID	Industry	Amount Billed Annual	Product Purchase Date
2	Bioplex	niri120ia	RUH5273	Higher Education	$ 49,434.00	12/24/2015
3	Bioplex	yjuu121et	RUH5273	Higher Education	$ 4,515.00	7/9/2014
4	Bioplex	frh1113zm	RUH5273	Higher Education	$ 39,854.00	10/13/2014
5	Bioplex	xydvl 16tu	RUH5273	Higher Education	$ 74,789.00	11/10/2015
6	Bioplex	a btb98wm	RUH5273	Higher Education	$ 81,724.00	10/8/2015
7	Bioplex	xcgz111se	RUH5273	Higher Education	$ 4,121.00	7/28/2015
8	Bioplex	orug114ew	RUH5273	Higher Education	$ 73,574.00	2/25/2014
9	Bioplex	mwavl17zn	RUH5273	Higher Education	$ 19,514.00	2/7/2014
10
11
12					$ 101,724.00	Sum of Feb & July
Sheet2

Cell Formulas
Range	Formula
E12	E12	=E3+E7+E8+E9