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.
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:OVLOOKUP(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:
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:OVLOOKUP(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!
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:OVLOOKUP(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:OVLOOKUP(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!