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!

 

[hernantorres23]

It is a part, what you want? dates between

Book1
	B	C	D	E	F										P	Q	R
1	Turnaround	Category	Upcharge	Discount	Date In										Total	Invoice	Try this
2	Expedited	Clean	None	New Client	19/12/2018										$84.80	1004	0.8
3	Standard	Clean	None	New Client	06/02/2019										$63.20	1005	0.4
4	Standard	Expert	None	None	11/02/2019										$36.90	1006	0.45
5	Standard	Clean	None	None	16/02/2019										$24.40	1007	0.4
6	Standard	Expert	None	None	18/02/2019										$52.65	1008	0.45
7	Standard	Clean	None	None	23/02/2019										$40.00	1009	0.4
8	Standard	Clean	None	None	24/02/2019										$43.20	1009	0.4
9	Rush	Clean	None	None	26/02/2019										$45.00	1009	0.6
10	Expedited	Clean	None	New Client	03/03/2019										$50.40	1010	0.8
Jobs

Cell Formulas
Range	Formula
R2:R10	R2	=SUMPRODUCT((Rates!$A$2:$A$16=B2)*(Rates!$B$2:$B$16=C2)*(((Rates!$E$2:$E$16)>=F2)*1)*(((Rates!$D$2:$D$16)<=F2*1)*Rates!$C$2:$C$16))

Book1
	A	B	C	D	E	F
1	Turnaround	Category	Rate	Start Date	End Date	Description
2	Standard	Clean	$0.40	17/08/2018	31/12/2019	Clean pages returned in 48-72 hours
3	Standard	Dirty	$0.50	17/08/2018	31/12/2019	Dirty pages returned in 48-72 hours
4	Standard	Expert	$0.45	17/08/2018	31/12/2019	Expert pages returned in 48-72 hours
5	Rush	Clean	$0.60	17/08/2018	31/12/2019	Clean pages returned in 24-48 hours
6	Rush	Dirty	$0.70	17/08/2018	31/12/2019	Dirty pages returned in 24-48 hours
7	Rush	Expert	$0.65	17/08/2018	31/12/2019	Expert pages returned in 24-48 hours
8	Expedited	Clean	$0.80	17/08/2018	31/12/2019	Clean pages returned in 12-24 hours
9	Expedited	Dirty	$0.95	17/08/2018	31/12/2019	Dirty pages returned in 12-24 hours
10	Expedited	Expert	$0.85	17/08/2018	31/12/2019	Expert pages returned in 12-24 hours
11	Daily	Clean	$1.00	17/08/2018	31/12/2019	Clean pages returned in 12 hours
12	Daily	Dirty	$1.05	17/08/2018	31/12/2019	Dirty pages returned in 12 hours
13	Daily	Expert	$1.10	17/08/2018	31/12/2019	Expert pages returned in 12 hours
14	Standard	Clean	$0.45	01/01/2020		Clean pages returned in 48-72 hours
15	Standard	Dirty	$0.55	01/01/2020		Dirty pages returned in 48-72 hours
16	Standard	Expert	$0.50	01/01/2020		Expert pages returned in 48-72 hours
Rates

 

[ncsushley]

It is a part, what you want? dates between

I'm not entirely sure what you're asking. The array formula needs to be updated to find the page rate with the Turnaround and Category where the Date In from Jobs falls between the Start Date and End Date on the Rates sheet. I need it to be part of the array formula in P1, not in a separate column. And it needs to be dynamic in that additional rates may be added to the Rates sheet in the future.

 

[hernantorres23]

Did you see the formula?
I only add it in a new column for demonstration purposes, but if after doing the test, that is correct, you can use it in your P column

=SUMPRODUCT((Rates!$A$2:$A$16=B2)*(Rates!$B$2:$B$16=C2)*(((Rates!$E$2:$E$16)>=F2)*1)*(((Rates!$D$2:$D$16)<=F2*1)*Rates!$C$2:$C$16))

As you can see in red highlight i'm try to add dates between column F

 

[ncsushley]

Did you see the formula?
I only add it in a new column for demonstration purposes, but if after doing the test, that is correct, you can use it in your P column

=SUMPRODUCT((Rates!$A$2:$A$16=B2)*(Rates!$B$2:$B$16=C2)*(((Rates!$E$2:$E$16)>=F2)*1)*(((Rates!$D$2:$D$16)<=F2*1)*Rates!$C$2:$C$16))

As you can see in red highlight i'm try to add dates between column F

I did try the above formula (with End Dates added to the Rates sheet) and it works when filled down a separate column, but it does not work in the array formula. When I put it in the array formula as written, it uses only the rate for the job in Row 2 instead of finding the correct rate for each row.

I tried modifying it like this, but then I got N/A results with "Error - Array arguments to EQ are of different size":

={"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) *
(SUMPRODUCT((Rates!A2:A=B2:B)*(Rates!B2:B=B2:C)*(((Rates!E2:E)>=F2:F)*1)*(((Rates!D2:D)<=F2:F*1)*Rates!C2:C)) +
VLOOKUP(D2:D, Upcharges!A:B, 2, 0)) * VLOOKUP(E2:E, Discounts!A:C, 3, 0)))}

 

[hernantorres23]

OK. Now I understand you.

In this case, I am trying to rephrase everything. If you verify those results (dates between) and they are correct. I would add the other conditions.
For now, I try to solve this by avoiding certain formulas, because they can generate compatibility problems.

Could you send me all the calculations(Column P) for following items?

Book1
	B	C	D	E	F										P
1	Turnaround	Category	Upcharge	Discount	Date In										Total
2	Expedited	Clean	None	New Client	19/12/2018										$84.80
3	Standard	Clean	None	New Client	06/02/2019										$63.20
4	Standard	Expert	None	None	11/02/2019										$36.90
Jobs

But don't worry, other guys may suggest other ways

 

[ncsushley]

OK. Now I understand you.

In this case, I am trying to rephrase everything. If you verify those results (dates between) and they are correct. I would add the other conditions.
For now, I try to solve this by avoiding certain formulas, because they can generate compatibility problems.

Could you send me all the calculations(Column P) for following items?

Book1
	B	C	D	E	F										P
1	Turnaround	Category	Upcharge	Discount	Date In										Total
2	Expedited	Clean	None	New Client	19/12/2018										$84.80
3	Standard	Clean	None	New Client	06/02/2019										$63.20
4	Standard	Expert	None	None	11/02/2019										$36.90
Jobs

But don't worry, other guys may suggest other ways

This is how those totals are calculated, along with a hypothetical job with a Date In after the rate increase:

Row 2
121 pages
- 15 pages (New Client Discount)
= 106 pages
x $0.80 rate (Expedited/Clean Rate as of 12/19/2018)
+ $0.00 (No Upcharge)
x 100% (No % Off Discount)
= $84.80

Row 3
173 pages
- 15 pages (New Client Discount)
= 158 pages
x $0.40 rate (Standard/Clean Rate as of 2/06/2019)
+ $0.00 (No Upcharge)
x 100% (No % Off Discount)
= $63.20

Row 4
82 pages
- 0 pages (No Pages Off Discount)
= 82 pages
x $0.45 rate (Standard/Expert Rate as of 2/11/2019)
+ $0.00 (No Upcharge)
x 100% (No % Off Discount)
= $36.90

Row 150 (Hypothetical job after rate increase)
10 pages
- 0 pages (No Pages Off Discount)
= 10 pages
x $0.45 rate (Standard/Clean Rate as of 1/5/2020)
+ $0.00 (No Upcharge)
x 100% (No % Off Discount)
= $4.50

 

[hernantorres23]

Hi.
Check this results

Book1
	B	C	D	E	F									O	P	Q	R
1	Turnaround	Category	Upcharge	Discount	Date In									Pages	Total	Invoice	Try this
2	Expedited	Clean	None	New Client	19/12/2018									121	$84.80	1004	84.80
3	Standard	Clean	None	New Client	06/02/2019									173	$63.20	1005	63.20
4	Standard	Expert	None	None	11/02/2019									82	$36.90	1006	36.90
150	Standard	Clean	None	None	05/01/2020									10			4.50
Jobs

Cell Formulas
Range	Formula
R2:R4, R150	R2	=(Jobs!O2-INDEX(tblDiscounts,MATCH(Jobs!E2,discount,0)+1,2))*SUMPRODUCT((turnaround=B2)*(category=C2)*(((end_date)>=F2)*1)*(((start_date)<=F2*1)*rate))+INDEX(tblUpcharges,MATCH(Jobs!D2,upcharge,0)+1,2)

Named Ranges
Name	Refers To	Cells
Z_6DDE7CCF_9ADF_4D97_949F_554536E02D02_.wvu.FilterData	=Jobs!$A$1:$AC$148	R2:R4

Cells with Data Validation
Cell	Allow	Criteria
B2:B155	List	=Rates!$A$2:$A$20
C2:C155	List	=Rates!$B$2:$B$20
D2:D155	List	=Upcharges!$A$2:$A$10
E2:E155	List	=Discounts!$A$2:$A$10

Book1
	A	B	C	D	E	F
1	Turnaround	Category	Rate	Start Date	End Date	Description
2	Standard	Clean	$0.40	17/08/2018	31/12/2019	Clean pages returned in 48-72 hours
3	Standard	Dirty	$0.50	17/08/2018	31/12/2019	Dirty pages returned in 48-72 hours
4	Standard	Expert	$0.45	17/08/2018	31/12/2019	Expert pages returned in 48-72 hours
5	Rush	Clean	$0.60	17/08/2018	31/12/2019	Clean pages returned in 24-48 hours
6	Rush	Dirty	$0.70	17/08/2018	31/12/2019	Dirty pages returned in 24-48 hours
7	Rush	Expert	$0.65	17/08/2018	31/12/2019	Expert pages returned in 24-48 hours
8	Expedited	Clean	$0.80	17/08/2018	31/12/2019	Clean pages returned in 12-24 hours
9	Expedited	Dirty	$0.95	17/08/2018	31/12/2019	Dirty pages returned in 12-24 hours
10	Expedited	Expert	$0.85	17/08/2018	31/12/2019	Expert pages returned in 12-24 hours
11	Daily	Clean	$1.00	17/08/2018	31/12/2019	Clean pages returned in 12 hours
12	Daily	Dirty	$1.05	17/08/2018	31/12/2019	Dirty pages returned in 12 hours
13	Daily	Expert	$1.10	17/08/2018	31/12/2019	Expert pages returned in 12 hours
14	Standard	Clean	$0.45	01/01/2020	05/01/2020	Clean pages returned in 48-72 hours
15	Standard	Dirty	$0.55	01/01/2020	05/01/2020	Dirty pages returned in 48-72 hours
16	Standard	Expert	$0.50	01/01/2020	05/01/2020	Expert pages returned in 48-72 hours
17
Rates

Book1
	A	B	C	D
1	Discount	Pages Off	% Billed	Description
2	New Client	15	100%	First 15 pages free
3	Referral	0	80%	20% off upon receipt of referral's first payment
4	CardURL	0	85%	15% off for mentioning TheBirdIsTheWord
5	Testimonial	0	90%	10% off for providing a testimonial
6	None	0	100%	Dropdown selection used if there is no discount
7
Discounts

Cells with Data Validation
Cell	Allow	Criteria
B2:B10	Whole number	>-1
C2:C10	Whole number	>0

Book1
	A	B	C
1	Upcharge	Cost Per Page	Description
2	28 Lines	$0.03	$0.03 per page for additional lines
3	None	$0.00	Dropdown selection used if there is no fee
4
Upcharges

Cells with Data Validation
Cell	Allow	Criteria
B2:B4	Whole number	>-1

The file here

 

[ncsushley]

I haven't quite wrapped my head around that solution yet, but I did notice some issues right off the bat:

• Named range functionality in Google Sheets is severely limited, so I'm not sure this would even work. Ranges need to be dynamic in order to include future jobs, rates, discounts, etc. They can't specify a row number to end on.
• The formula does not account for jobs with the New Client discount where Pages is less than Pages Off. If Pages = 4 and Pages Off = 15, the formula should use 0 for Pages to avoid a negative total.
• It also does not account for jobs with discounts that deduct a percentage rather than a number of pages. The total for a job with the Testimonial discount should be multiplied by 90% to take 10% off.

 

[hernantorres23]

Now, I think that all results are correct

Book1
	B	C	D	E	F									O	P		R
1	Turnaround	Category	Upcharge	Discount	Date In									Pages	Total		Try this
2	Expedited	Clean	None	New Client	19/12/2018									121	$84.80		84.80
3	Standard	Clean	None	New Client	06/02/2019									173	$63.20		63.20
4	Standard	Expert	None	None	11/02/2019									82	$36.90		36.90
16	Expedited	Clean	None	Testimonial	16/03/2019									44	$31.68		31.68
41	Standard	Clean	None	New Client	20/05/2019									4	$0.00		0.00
150	Standard	Clean	None	None	05/01/2020									10	$4.50		4.50
Jobs

Cell Formulas
Range	Formula
R2:R4, R16, R41, R150	R2	=IF(AND(O2<=15,E2="New Client"),0,((O2-INDEX(tblDiscounts,MATCH(E2,discount,0)+1,2))*SUMPRODUCT((turnaround=B2)*(category=C2)*(((end_date)>=F2)*1)*(((start_date)<=F2*1)*rate))+INDEX(tblUpcharges,MATCH(D2,upcharge,0)+1,2))*INDEX(tblDiscounts,MATCH(E2,discount,0)+1,3))

Now, let my try in Google Sheets