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



## ncsushley (Dec 29, 2019)

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.


BCDEFOP1*Turnaround**Category**Upcharge**Discount**Date In**Pages**Total*2ExpeditedCleanNoneNew Client12/19/2018121$84.803StandardCleanNoneNew Client02/06/2019173$63.204StandardExpertNoneNone02/11/201982$36.905StandardCleanNoneNone02/16/201961$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:


ABCDE*Turnaround**Category**Rate**Start Date**End Date*StandardClean$0.408/17/201812/31/2019StandardDirty$0.508/17/201812/31/2019StandardExpert$0.458/17/201812/31/2019ExpeditedClean$0.808/17/2018ExpeditedDirty$0.958/17/2018ExpeditedExpert$0.858/17/2018StandardClean$0.451/1/2020StandardDirty$0.551/1/2020StandardExpert$0.501/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 (Dec 29, 2019)

It is a part, what you want? dates between
Book1BCDEFPQR1TurnaroundCategoryUpchargeDiscountDate InTotalInvoiceTry this2ExpeditedCleanNoneNew Client19/12/2018$84.8010040.83StandardCleanNoneNew Client06/02/2019$63.2010050.44StandardExpertNoneNone11/02/2019$36.9010060.455StandardCleanNoneNone16/02/2019$24.4010070.46StandardExpertNoneNone18/02/2019$52.6510080.457StandardCleanNoneNone23/02/2019$40.0010090.48StandardCleanNoneNone24/02/2019$43.2010090.49RushCleanNoneNone26/02/2019$45.0010090.610ExpeditedCleanNoneNew Client03/03/2019$50.4010100.8JobsCell FormulasRangeFormulaR2: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))

Book1ABCDEF1TurnaroundCategoryRateStart DateEnd DateDescription2StandardClean$0.4017/08/201831/12/2019Clean pages returned in 48-72 hours3StandardDirty$0.5017/08/201831/12/2019Dirty pages returned in 48-72 hours4StandardExpert$0.4517/08/201831/12/2019Expert pages returned in 48-72 hours5RushClean$0.6017/08/201831/12/2019Clean pages returned in 24-48 hours6RushDirty$0.7017/08/201831/12/2019Dirty pages returned in 24-48 hours7RushExpert$0.6517/08/201831/12/2019Expert pages returned in 24-48 hours8ExpeditedClean$0.8017/08/201831/12/2019Clean pages returned in 12-24 hours9ExpeditedDirty$0.9517/08/201831/12/2019Dirty pages returned in 12-24 hours10ExpeditedExpert$0.8517/08/201831/12/2019Expert pages returned in 12-24 hours11DailyClean$1.0017/08/201831/12/2019Clean pages returned in 12 hours12DailyDirty$1.0517/08/201831/12/2019Dirty pages returned in 12 hours13DailyExpert$1.1017/08/201831/12/2019Expert pages returned in 12 hours14StandardClean$0.4501/01/2020Clean pages returned in 48-72 hours15StandardDirty$0.5501/01/2020Dirty pages returned in 48-72 hours16StandardExpert$0.5001/01/2020Expert pages returned in 48-72 hoursRates


----------



## ncsushley (Dec 30, 2019)

hernantorres23 said:


> 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 (Dec 30, 2019)

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 (Dec 30, 2019)

hernantorres23 said:


> 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))
> ...



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 (Dec 30, 2019)

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?
Book1BCDEFP1TurnaroundCategoryUpchargeDiscountDate InTotal2ExpeditedCleanNoneNew Client19/12/2018$84.803StandardCleanNoneNew Client06/02/2019$63.204StandardExpertNoneNone11/02/2019$36.90Jobs

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


----------



## ncsushley (Dec 30, 2019)

hernantorres23 said:


> 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.
> ...



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 (Dec 30, 2019)

Hi.
Check this results
Book1BCDEFOPQR1TurnaroundCategoryUpchargeDiscountDate InPagesTotalInvoiceTry this2ExpeditedCleanNoneNew Client19/12/2018121$84.80100484.80 3StandardCleanNoneNew Client06/02/2019173$63.20100563.20 4StandardExpertNoneNone11/02/201982$36.90100636.90 150StandardCleanNoneNone05/01/2020104.50 JobsCell FormulasRangeFormulaR2: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 RangesNameRefers ToCellsZ_6DDE7CCF_9ADF_4D97_949F_554536E02D02_.wvu.FilterData=Jobs!$A$1:$AC$148R2:R4Cells with Data ValidationCellAllowCriteriaB2:B155List=Rates!$A$2:$A$20C2:C155List=Rates!$B$2:$B$20D2:D155List=Upcharges!$A$2:$A$10E2:E155List=Discounts!$A$2:$A$10

Book1ABCDEF1TurnaroundCategoryRateStart DateEnd DateDescription2StandardClean$0.4017/08/201831/12/2019Clean pages returned in 48-72 hours3StandardDirty$0.5017/08/201831/12/2019Dirty pages returned in 48-72 hours4StandardExpert$0.4517/08/201831/12/2019Expert pages returned in 48-72 hours5RushClean$0.6017/08/201831/12/2019Clean pages returned in 24-48 hours6RushDirty$0.7017/08/201831/12/2019Dirty pages returned in 24-48 hours7RushExpert$0.6517/08/201831/12/2019Expert pages returned in 24-48 hours8ExpeditedClean$0.8017/08/201831/12/2019Clean pages returned in 12-24 hours9ExpeditedDirty$0.9517/08/201831/12/2019Dirty pages returned in 12-24 hours10ExpeditedExpert$0.8517/08/201831/12/2019Expert pages returned in 12-24 hours11DailyClean$1.0017/08/201831/12/2019Clean pages returned in 12 hours12DailyDirty$1.0517/08/201831/12/2019Dirty pages returned in 12 hours13DailyExpert$1.1017/08/201831/12/2019Expert pages returned in 12 hours14StandardClean$0.4501/01/202005/01/2020Clean pages returned in 48-72 hours15StandardDirty$0.5501/01/202005/01/2020Dirty pages returned in 48-72 hours16StandardExpert$0.5001/01/202005/01/2020Expert pages returned in 48-72 hours17Rates

Book1ABCD1DiscountPages Off% BilledDescription2New Client15100%First 15 pages free3Referral080%20% off upon receipt of referral's first payment4CardURL085%15% off for mentioning TheBirdIsTheWord5Testimonial090%10% off for providing a testimonial6None0100%Dropdown selection used if there is no discount7DiscountsCells with Data ValidationCellAllowCriteriaB2:B10Whole number>-1C2:C10Whole number>0

Book1ABC1UpchargeCost Per PageDescription228 Lines$0.03$0.03 per page for additional lines3None$0.00Dropdown selection used if there is no fee4UpchargesCells with Data ValidationCellAllowCriteriaB2:B4Whole number>-1

The file *here*


----------



## ncsushley (Dec 31, 2019)

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 (Dec 31, 2019)

Now, I think that all results are correct

Book1BCDEFOPR1TurnaroundCategoryUpchargeDiscountDate InPagesTotalTry this2ExpeditedCleanNoneNew Client19/12/2018121$84.8084.80 3StandardCleanNoneNew Client06/02/2019173$63.2063.20 4StandardExpertNoneNone11/02/201982$36.9036.90 16ExpeditedCleanNoneTestimonial16/03/201944$31.6831.68 41StandardCleanNoneNew Client20/05/20194$0.000.00 150StandardCleanNoneNone05/01/202010$4.504.50 JobsCell FormulasRangeFormulaR2: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


----------



## ncsushley (Dec 29, 2019)

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.


BCDEFOP1*Turnaround**Category**Upcharge**Discount**Date In**Pages**Total*2ExpeditedCleanNoneNew Client12/19/2018121$84.803StandardCleanNoneNew Client02/06/2019173$63.204StandardExpertNoneNone02/11/201982$36.905StandardCleanNoneNone02/16/201961$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:


ABCDE*Turnaround**Category**Rate**Start Date**End Date*StandardClean$0.408/17/201812/31/2019StandardDirty$0.508/17/201812/31/2019StandardExpert$0.458/17/201812/31/2019ExpeditedClean$0.808/17/2018ExpeditedDirty$0.958/17/2018ExpeditedExpert$0.858/17/2018StandardClean$0.451/1/2020StandardDirty$0.551/1/2020StandardExpert$0.501/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 (Dec 31, 2019)

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 (Dec 31, 2019)

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.


----------

