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

ncsushley

New Member
Joined
Dec 29, 2019
Messages
7
Office Version
2013
Platform
Windows
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.

BCDEFOP
1TurnaroundCategoryUpchargeDiscountDate InPagesTotal
2ExpeditedCleanNoneNew Client12/19/2018121$84.80
3StandardCleanNoneNew Client02/06/2019173$63.20
4StandardExpertNoneNone02/11/201982$36.90
5StandardCleanNoneNone02/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
TurnaroundCategoryRateStart DateEnd Date
StandardClean$0.408/17/201812/31/2019
StandardDirty$0.508/17/201812/31/2019
StandardExpert$0.458/17/201812/31/2019
ExpeditedClean$0.808/17/2018
ExpeditedDirty$0.958/17/2018
ExpeditedExpert$0.858/17/2018
StandardClean$0.451/1/2020
StandardDirty$0.551/1/2020
StandardExpert$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

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
It is a part, what you want? dates between
xl2bb.xlam
BCDEFPQR
1TurnaroundCategoryUpchargeDiscountDate InTotalInvoiceTry this
2ExpeditedCleanNoneNew Client19/12/2018$84.801004
3StandardCleanNoneNew Client06/02/2019$63.201005
4StandardExpertNoneNone11/02/2019$36.901006
5StandardCleanNoneNone16/02/2019$24.401007
6StandardExpertNoneNone18/02/2019$52.651008
7StandardCleanNoneNone23/02/2019$40.001009
8StandardCleanNoneNone24/02/2019$43.201009
9RushCleanNoneNone26/02/2019$45.001009
10ExpeditedCleanNoneNew Client03/03/2019$50.401010
Jobs
Cell Formulas
Range(s)Formula
R2:R10R2=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))


xl2bb.xlam
ABCDEF
1TurnaroundCategoryRateStart DateEnd DateDescription
2StandardClean$0.4017/08/201831/12/2019Clean pages returned in 48-72 hours
3StandardDirty$0.5017/08/201831/12/2019Dirty pages returned in 48-72 hours
4StandardExpert$0.4517/08/201831/12/2019Expert pages returned in 48-72 hours
5RushClean$0.6017/08/201831/12/2019Clean pages returned in 24-48 hours
6RushDirty$0.7017/08/201831/12/2019Dirty pages returned in 24-48 hours
7RushExpert$0.6517/08/201831/12/2019Expert pages returned in 24-48 hours
8ExpeditedClean$0.8017/08/201831/12/2019Clean pages returned in 12-24 hours
9ExpeditedDirty$0.9517/08/201831/12/2019Dirty pages returned in 12-24 hours
10ExpeditedExpert$0.8517/08/201831/12/2019Expert pages returned in 12-24 hours
11DailyClean$1.0017/08/201831/12/2019Clean pages returned in 12 hours
12DailyDirty$1.0517/08/201831/12/2019Dirty pages returned in 12 hours
13DailyExpert$1.1017/08/201831/12/2019Expert pages returned in 12 hours
14StandardClean$0.4501/01/2020Clean pages returned in 48-72 hours
15StandardDirty$0.5501/01/2020Dirty pages returned in 48-72 hours
16StandardExpert$0.5001/01/2020Expert pages returned in 48-72 hours
Rates
 

ncsushley

New Member
Joined
Dec 29, 2019
Messages
7
Office Version
2013
Platform
Windows
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

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
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
 
Last edited:

ncsushley

New Member
Joined
Dec 29, 2019
Messages
7
Office Version
2013
Platform
Windows
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

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
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?
xl2bb.xlam
BCDEFP
1TurnaroundCategoryUpchargeDiscountDate InTotal
2ExpeditedCleanNoneNew Client19/12/2018$84.80
3StandardCleanNoneNew Client06/02/2019$63.20
4StandardExpertNoneNone11/02/2019$36.90
Jobs


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

ncsushley

New Member
Joined
Dec 29, 2019
Messages
7
Office Version
2013
Platform
Windows
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?
xl2bb.xlam
BCDEFP
1TurnaroundCategoryUpchargeDiscountDate InTotal
2ExpeditedCleanNoneNew Client19/12/2018$84.80
3StandardCleanNoneNew Client06/02/2019$63.20
4StandardExpertNoneNone11/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

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
Hi.
Check this results
xl2bb.xlam
BCDEFOPQR
1TurnaroundCategoryUpchargeDiscountDate InPagesTotalInvoiceTry this
2ExpeditedCleanNoneNew Client19/12/2018121$84.801004
3StandardCleanNoneNew Client06/02/2019173$63.201005
4StandardExpertNoneNone11/02/201982$36.901006
150StandardCleanNoneNone05/01/202010
Jobs
Cell Formulas
Range(s)Formula
R2:R4, R150R2=(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
NameRefers ToCells
Z_6DDE7CCF_9ADF_4D97_949F_554536E02D02_.wvu.FilterData=Jobs!$A$1:$AC$148R2:R4
Cells with Data Validation
CellAllowCriteria
B2:B155List=Rates!$A$2:$A$20
C2:C155List=Rates!$B$2:$B$20
D2:D155List=Upcharges!$A$2:$A$10
E2:E155List=Discounts!$A$2:$A$10


xl2bb.xlam
ABCDEF
1TurnaroundCategoryRateStart DateEnd DateDescription
2StandardClean$0.4017/08/201831/12/2019Clean pages returned in 48-72 hours
3StandardDirty$0.5017/08/201831/12/2019Dirty pages returned in 48-72 hours
4StandardExpert$0.4517/08/201831/12/2019Expert pages returned in 48-72 hours
5RushClean$0.6017/08/201831/12/2019Clean pages returned in 24-48 hours
6RushDirty$0.7017/08/201831/12/2019Dirty pages returned in 24-48 hours
7RushExpert$0.6517/08/201831/12/2019Expert pages returned in 24-48 hours
8ExpeditedClean$0.8017/08/201831/12/2019Clean pages returned in 12-24 hours
9ExpeditedDirty$0.9517/08/201831/12/2019Dirty pages returned in 12-24 hours
10ExpeditedExpert$0.8517/08/201831/12/2019Expert pages returned in 12-24 hours
11DailyClean$1.0017/08/201831/12/2019Clean pages returned in 12 hours
12DailyDirty$1.0517/08/201831/12/2019Dirty pages returned in 12 hours
13DailyExpert$1.1017/08/201831/12/2019Expert pages returned in 12 hours
14StandardClean$0.4501/01/202005/01/2020Clean pages returned in 48-72 hours
15StandardDirty$0.5501/01/202005/01/2020Dirty pages returned in 48-72 hours
16StandardExpert$0.5001/01/202005/01/2020Expert pages returned in 48-72 hours
17
Rates


xl2bb.xlam
ABCD
1DiscountPages Off% BilledDescription
2New Client15100%First 15 pages free
3Referral080%20% off upon receipt of referral's first payment
4CardURL085%15% off for mentioning TheBirdIsTheWord
5Testimonial090%10% off for providing a testimonial
6None0100%Dropdown selection used if there is no discount
7
Discounts
Cells with Data Validation
CellAllowCriteria
B2:B10Whole number>-1
C2:C10Whole number>0


xl2bb.xlam
ABC
1UpchargeCost Per PageDescription
228 Lines$0.03$0.03 per page for additional lines
3None$0.00Dropdown selection used if there is no fee
4
Upcharges
Cells with Data Validation
CellAllowCriteria
B2:B4Whole number>-1


The file here
 

ncsushley

New Member
Joined
Dec 29, 2019
Messages
7
Office Version
2013
Platform
Windows
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

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
Now, I think that all results are correct

xl2bb.xlam
BCDEFOPR
1TurnaroundCategoryUpchargeDiscountDate InPagesTotalTry this
2ExpeditedCleanNoneNew Client19/12/2018121$84.80
3StandardCleanNoneNew Client06/02/2019173$63.20
4StandardExpertNoneNone11/02/201982$36.90
16ExpeditedCleanNoneTestimonial16/03/201944$31.68
41StandardCleanNoneNew Client20/05/20194$0.00
150StandardCleanNoneNone05/01/202010$4.50
Jobs
Cell Formulas
Range(s)Formula
R2:R4, R16, R41, R150R2=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
 

Forum statistics

Threads
1,081,641
Messages
5,360,207
Members
400,577
Latest member
crocki

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top