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]

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

But the problem is that just adds up the annual amount that is listed. Bioplex is in the "Higher Education" industry, and therefore it is billed quarterly, so I need to take that into consideration in my total billed amount formula. Also, since the purchases began on 2014 and 2015, and I am asked to find the total amount billed between any two months of 2017, I'm assuming I have to creating a billing cycle into 2017, which I am confused on how to do.

 

[AhoyNC]

The only think I can suggest is that with customer Bioplex you figure out what the annual amount is and divide by 4 to get the quarterly amount. Then assuming no price increase and products stay the same. For Feb. it would just be the quarterly price. For July it would be 3 times the quarterly price to get amount billed.

 

[Akhalkalak]

The only think I can suggest is that with customer Bioplex you figure out what the annual amount is and divide by 4 to get the quarterly amount. Then assuming no price increase and products stay the same. For Feb. it would just be the quarterly price. For July it would be 3 times the quarterly price to get amount billed.

So I have the amount to be billed per frequency per customer, but how would I incorporate this into one big formula that pulls that number based on the date range and customer?

 

[AhoyNC]

How about showing us what you have and then an actual example. So, pick a customer and a date range and show us what amount you expect and why. Then me may be able to help you with a formula.

 

[Akhalkalak]

How about showing us what you have and then an actual example. So, pick a customer and a date range and show us what amount you expect and why. Then me may be able to help you with a formula.

Here is an example where I try to find the total amount to be billed to Bioplex between February - July, 2017
I do not want it to just sum up the annual amounts in column E, I need it to take into consideration the billing frequency, for example, in this Bioplex case, I want it to take the total amount billed between February and July, but factor in the fact that the annual amount shown is billed quarterly, so every 3 months, per product based on each products respective purchase date

I added these columns that calculate the next billing frequency per customer based on their industry, but not sure if there is a more efficient way to do it, I tried to make a data table but was unable to.

 

[AhoyNC]

So would the answer be $76,293.00.
Number in cell I4 in the example below will change as industry changes.
If this works I'll leave up to you to figure cell I7 based on the industry.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Customer Name	Product ID	Customer ID	Industry	Amount Billed Annual	Product Purchase Date		Customer	Start Month	End Month
2	Betasoloin	fdw115rt	OLX3928	Commercial Property Mgmt	31669	6/8/2015		Bioplex	2	7
3	Betasoloin	qbdt1041g	OLX3928	Commercial Property Mgmt	8756	5/15/2014		Higher Education	Quarterly
4	Betasoloin	auuh99kd	OLX3928	Commercial Property Mgmt	43267	7/11/2014		Amount Quarterly	4
5	Betasoloin	htoi103fe	OLX3928	Commercial Property Mgmt	71194	3/19/2014		$ 25,431.00
6	Betatech	pmad110jy	ENC2036	Higher Education	61112	3/24/2014		Number of Qtrs
7	Betatech	nhon112tr	ENC2036	Higher Education	45694	5/7/2014		3	$ 76,293.00
8	Betatech	ahws101jq	ENC2036	Higher Education	52449	4/20/2014
9	Betatech	bod117za	ENC2036	Higher Education	7294	1/25/2014
10	Bioholding	bqkm112wm	PSF4581	Corporate	37116	2/19/2015		Indusry	Billing Frequency	Frequency
11	Bioholding	chot112re	PSF4581	Corporate	62209	11/25/2014		Commercial Property Mgmt	Monthly	12
12	Bioholding	cwla115sb	PSF4581	Corporate	37429	6/7/2015		Higher Education	Quarterly	4
13	Bioholding	cljx118ai	PSF4581	Corporate	65503	9/28/2015		Corporate	Annally	1
14	Bioplex	niri120ia	RUH5273	Higher Education	49434	12/24/2015		Retail	Monthly	12
15	Bioplex	yjuu121et	RUH5273	Higher Education	4515	7/9/2014		Wireless	Monthly	12
16	Bioplex	frh1113zm	RUH5273	Higher Education	39854	10/13/2014		Healthcare	Quarterly	4
17	Bioplex	xydvl 16tu	RUH5273	Higher Education	74789	11/10/2015
18	Bioplex	a btb98wm	RUH5273	Higher Education	81724	10/8/2015
19	Bioplex	xcgz111se	RUH5273	Higher Education	4121	7/28/2015
20	Bioplex	orug114ew	RUH5273	Higher Education	73574	2/25/2014
21	Bioplex	mwavl17zn	RUH5273	Higher Education	19514	2/7/2014
22	Blackzim	quwa109rnw	MZV8706	Retail	52007	4/26/2015
23	Blackzim	coqd112fm	MZV8706	Retail	78365	2/24/2014
24	Blackzim	wuzql 17ke	MZV8706	Retail	97284	4/2/2015
25	Blackzim	tdds108zh	MZV8706	Retail	77778	5/23/2015
26	Blackzim	hgdm108kv	MZV8706	Retail	21270	2/2/2014
27	Blackzim	astc99xy	MZV8706	Retail	60753	7/16/2014
28	Cancity	hsab119cq	WR15364	Higher Education	94123	9/25/2014
29	Cancity	vde1116re	WR15364	Higher Education	89894	11/12/2015
30	Cancity	jwqz98zk	WR15364	Higher Education	23473	12/10/2015
31	Cancity	yxwb109nq	WR15364	Higher Education	64573	12/26/2014
Sheet4

Cell Formulas
Range	Formula
I3	I3	=XLOOKUP(XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31),$H$11:$H$16,$I$11:$I$16)
I4	I4	=XLOOKUP(XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31),$H$11:$H$16,$J$11:$J$16)
H3	H3	=XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31)
H4	H4	="Amount "&$I$3
H5	H5	=SUM(FILTER($E$2:$E$31,($A$2:$A$31=$H$2)*(MONTH($F$2:$F$31)>=$I$2)*(MONTH($F$2:$F$31)<=$J$2)))/I4
H7	H7	=(IFS($J$2<=3,1,$J$2<=6,2,$J$2<=9,3,$J$2<=12,4)-IFS($I$2<=3,1,$I$2<=6,2,$I$2<=9,3,$I$2<=12,4))+1
I7	I7	=H5*H7

 

[Akhalkalak]

So would the answer be $76,293.00.
Number in cell I4 in the example below will change as industry changes.
If this works I'll leave up to you to figure cell I7 based on the industry.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Customer Name	Product ID	Customer ID	Industry	Amount Billed Annual	Product Purchase Date		Customer	Start Month	End Month
2	Betasoloin	fdw115rt	OLX3928	Commercial Property Mgmt	31669	6/8/2015		Bioplex	2	7
3	Betasoloin	qbdt1041g	OLX3928	Commercial Property Mgmt	8756	5/15/2014		Higher Education	Quarterly
4	Betasoloin	auuh99kd	OLX3928	Commercial Property Mgmt	43267	7/11/2014		Amount Quarterly	4
5	Betasoloin	htoi103fe	OLX3928	Commercial Property Mgmt	71194	3/19/2014		$ 25,431.00
6	Betatech	pmad110jy	ENC2036	Higher Education	61112	3/24/2014		Number of Qtrs
7	Betatech	nhon112tr	ENC2036	Higher Education	45694	5/7/2014		3	$ 76,293.00
8	Betatech	ahws101jq	ENC2036	Higher Education	52449	4/20/2014
9	Betatech	bod117za	ENC2036	Higher Education	7294	1/25/2014
10	Bioholding	bqkm112wm	PSF4581	Corporate	37116	2/19/2015		Indusry	Billing Frequency	Frequency
11	Bioholding	chot112re	PSF4581	Corporate	62209	11/25/2014		Commercial Property Mgmt	Monthly	12
12	Bioholding	cwla115sb	PSF4581	Corporate	37429	6/7/2015		Higher Education	Quarterly	4
13	Bioholding	cljx118ai	PSF4581	Corporate	65503	9/28/2015		Corporate	Annally	1
14	Bioplex	niri120ia	RUH5273	Higher Education	49434	12/24/2015		Retail	Monthly	12
15	Bioplex	yjuu121et	RUH5273	Higher Education	4515	7/9/2014		Wireless	Monthly	12
16	Bioplex	frh1113zm	RUH5273	Higher Education	39854	10/13/2014		Healthcare	Quarterly	4
17	Bioplex	xydvl 16tu	RUH5273	Higher Education	74789	11/10/2015
18	Bioplex	a btb98wm	RUH5273	Higher Education	81724	10/8/2015
19	Bioplex	xcgz111se	RUH5273	Higher Education	4121	7/28/2015
20	Bioplex	orug114ew	RUH5273	Higher Education	73574	2/25/2014
21	Bioplex	mwavl17zn	RUH5273	Higher Education	19514	2/7/2014
22	Blackzim	quwa109rnw	MZV8706	Retail	52007	4/26/2015
23	Blackzim	coqd112fm	MZV8706	Retail	78365	2/24/2014
24	Blackzim	wuzql 17ke	MZV8706	Retail	97284	4/2/2015
25	Blackzim	tdds108zh	MZV8706	Retail	77778	5/23/2015
26	Blackzim	hgdm108kv	MZV8706	Retail	21270	2/2/2014
27	Blackzim	astc99xy	MZV8706	Retail	60753	7/16/2014
28	Cancity	hsab119cq	WR15364	Higher Education	94123	9/25/2014
29	Cancity	vde1116re	WR15364	Higher Education	89894	11/12/2015
30	Cancity	jwqz98zk	WR15364	Higher Education	23473	12/10/2015
31	Cancity	yxwb109nq	WR15364	Higher Education	64573	12/26/2014
Sheet4

Cell Formulas
Range	Formula
I3	I3	=XLOOKUP(XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31),$H$11:$H$16,$I$11:$I$16)
I4	I4	=XLOOKUP(XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31),$H$11:$H$16,$J$11:$J$16)
H3	H3	=XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31)
H4	H4	="Amount "&$I$3
H5	H5	=SUM(FILTER($E$2:$E$31,($A$2:$A$31=$H$2)*(MONTH($F$2:$F$31)>=$I$2)*(MONTH($F$2:$F$31)<=$J$2)))/I4
H7	H7	=(IFS($J$2<=3,1,$J$2<=6,2,$J$2<=9,3,$J$2<=12,4)-IFS($I$2<=3,1,$I$2<=6,2,$I$2<=9,3,$I$2<=12,4))+1
I7	I7	=H5*H7

I will take a look and get back to you, thank you so much for your help so far!

 

[Akhalkalak]

I will take a look and get back to you, thank you so much for your help so far!

So would the answer be $76,293.00.
Number in cell I4 in the example below will change as industry changes.
If this works I'll leave up to you to figure cell I7 based on the industry.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Customer Name	Product ID	Customer ID	Industry	Amount Billed Annual	Product Purchase Date		Customer	Start Month	End Month
2	Betasoloin	fdw115rt	OLX3928	Commercial Property Mgmt	31669	6/8/2015		Bioplex	2	7
3	Betasoloin	qbdt1041g	OLX3928	Commercial Property Mgmt	8756	5/15/2014		Higher Education	Quarterly
4	Betasoloin	auuh99kd	OLX3928	Commercial Property Mgmt	43267	7/11/2014		Amount Quarterly	4
5	Betasoloin	htoi103fe	OLX3928	Commercial Property Mgmt	71194	3/19/2014		$ 25,431.00
6	Betatech	pmad110jy	ENC2036	Higher Education	61112	3/24/2014		Number of Qtrs
7	Betatech	nhon112tr	ENC2036	Higher Education	45694	5/7/2014		3	$ 76,293.00
8	Betatech	ahws101jq	ENC2036	Higher Education	52449	4/20/2014
9	Betatech	bod117za	ENC2036	Higher Education	7294	1/25/2014
10	Bioholding	bqkm112wm	PSF4581	Corporate	37116	2/19/2015		Indusry	Billing Frequency	Frequency
11	Bioholding	chot112re	PSF4581	Corporate	62209	11/25/2014		Commercial Property Mgmt	Monthly	12
12	Bioholding	cwla115sb	PSF4581	Corporate	37429	6/7/2015		Higher Education	Quarterly	4
13	Bioholding	cljx118ai	PSF4581	Corporate	65503	9/28/2015		Corporate	Annally	1
14	Bioplex	niri120ia	RUH5273	Higher Education	49434	12/24/2015		Retail	Monthly	12
15	Bioplex	yjuu121et	RUH5273	Higher Education	4515	7/9/2014		Wireless	Monthly	12
16	Bioplex	frh1113zm	RUH5273	Higher Education	39854	10/13/2014		Healthcare	Quarterly	4
17	Bioplex	xydvl 16tu	RUH5273	Higher Education	74789	11/10/2015
18	Bioplex	a btb98wm	RUH5273	Higher Education	81724	10/8/2015
19	Bioplex	xcgz111se	RUH5273	Higher Education	4121	7/28/2015
20	Bioplex	orug114ew	RUH5273	Higher Education	73574	2/25/2014
21	Bioplex	mwavl17zn	RUH5273	Higher Education	19514	2/7/2014
22	Blackzim	quwa109rnw	MZV8706	Retail	52007	4/26/2015
23	Blackzim	coqd112fm	MZV8706	Retail	78365	2/24/2014
24	Blackzim	wuzql 17ke	MZV8706	Retail	97284	4/2/2015
25	Blackzim	tdds108zh	MZV8706	Retail	77778	5/23/2015
26	Blackzim	hgdm108kv	MZV8706	Retail	21270	2/2/2014
27	Blackzim	astc99xy	MZV8706	Retail	60753	7/16/2014
28	Cancity	hsab119cq	WR15364	Higher Education	94123	9/25/2014
29	Cancity	vde1116re	WR15364	Higher Education	89894	11/12/2015
30	Cancity	jwqz98zk	WR15364	Higher Education	23473	12/10/2015
31	Cancity	yxwb109nq	WR15364	Higher Education	64573	12/26/2014
Sheet4

Cell Formulas
Range	Formula
I3	I3	=XLOOKUP(XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31),$H$11:$H$16,$I$11:$I$16)
I4	I4	=XLOOKUP(XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31),$H$11:$H$16,$J$11:$J$16)
H3	H3	=XLOOKUP($H$2,$A$2:$A$31,$D$2:$D$31)
H4	H4	="Amount "&$I$3
H5	H5	=SUM(FILTER($E$2:$E$31,($A$2:$A$31=$H$2)*(MONTH($F$2:$F$31)>=$I$2)*(MONTH($F$2:$F$31)<=$J$2)))/I4
H7	H7	=(IFS($J$2<=3,1,$J$2<=6,2,$J$2<=9,3,$J$2<=12,4)-IFS($I$2<=3,1,$I$2<=6,2,$I$2<=9,3,$I$2<=12,4))+1
I7	I7	=H5*H7

So this is good, however, I noticed an issue, let's say I put the months as 1-12 for January - December, I am getting an incorrect value and I also noticed the same when doing just a few months. I think this has to do with the last formula that is linked with the number of quarters.

 

[AhoyNC]

You need to show an example or 2 of what's wrong and what the answer should be.