Array formula with lookup based on multiple criteria including a date range

[ncsushley]

I tried to find help with this question on Stack Overflow here but did not get a complete answer.

I use this Google sheet to track proofreading jobs. I'm trying to modify the array formula in P1 that calculates the billable total for each job. The formula currently uses the Turnaround and Category to look up the page rate for each job. I plan to raise some of my rates in January, so I'm trying to modify the formula to also check if the job's Date In falls between the rate's Start Date and End Date.

	B	C	D	E	F	O	P
1	Turnaround	Category	Upcharge	Discount	Date In	Pages	Total
2	Expedited	Clean	None	New Client	12/19/2018	121	$84.80
3	Standard	Clean	None	New Client	02/06/2019	173	$63.20
4	Standard	Expert	None	None	02/11/2019	82	$36.90
5	Standard	Clean	None	None	02/16/2019	61	$24.40

The array formula in P1 looks like this, and the red part looking up the page rate is what I need to modify:

={"Total"; ARRAYFORMULA(IF(ISBLANK(O2:O),"",
IF(O2:O>VLOOKUP(E2:E, Discounts!A:B, 2, 0),
O2:O-VLOOKUP(E2:E, Discounts!A:B, 2, 0), 0)*
(VLOOKUP(B2:B&C2:C, {Rates!A2:A30&Rates!B2:B30, Rates!C2:C30}, 2, 0)+
VLOOKUP(D2:D, Upcharges!A:B, 2, 0))*VLOOKUP(E2:E, Discounts!A:C, 3, 0)))}

My Rates sheet looks like this:

A	B	C	D	E
Turnaround	Category	Rate	Start Date	End Date
Standard	Clean	$0.40	8/17/2018	12/31/2019
Standard	Dirty	$0.50	8/17/2018	12/31/2019
Standard	Expert	$0.45	8/17/2018	12/31/2019
Expedited	Clean	$0.80	8/17/2018
Expedited	Dirty	$0.95	8/17/2018
Expedited	Expert	$0.85	8/17/2018
Standard	Clean	$0.45	1/1/2020
Standard	Dirty	$0.55	1/1/2020
Standard	Expert	$0.50	1/1/2020

I tried using SUMIFS to pull the rate, but that returned 0 for the entire column:

={"Total"; ARRAYFORMULA(IF(ISBLANK(O2:O),"",
IF(O2:O>VLOOKUP(E2:E, Discounts!A:B, 2, 0),
O2:O-VLOOKUP(E2:E, Discounts!A:B, 2, 0), 0)*
(SUMIFS(Rates!C2:C,Rates!A2:A,B2:B,Rates!B2:B,C2:C,Rates!D2:D,">="&F2:F,Rates!E2:E,"<="&F2:F)+
VLOOKUP(D2:D, Upcharges!A:B, 2, 0))*VLOOKUP(E2:E, Discounts!A:C, 3, 0)))}


A sandbox copy of the workbook is available here. I appreciate any help!

 

[ncsushley]

Feel free to make a copy of the Jobs sheet in that sandbox workbook and try your solution directly in the array formula. I appreciate your help!

 

[ncsushley]

I am trying to avoid hard-coding any numbers or anything into formulas. I could potentially add discounts in the future that are similar to the New Client discount with different values, so ideally the formula would simply check the Pages against the Pages Off for whichever discount is selected and either use Pages minus Pages Off or 0.