CODEWITHSUNDEEP
google-sheetssumgoogle-sheets-formulaarray-formulasgs-vlookup
RonV
RonV

Reputation: 13

Tiered Discount Price calculation Google Sheets formula

I'm trying to figure out how to calculate a pricing subtotal based on tiered discount. I found various examples online for Excel but was not able to grok it and make it work in Google Sheets.

What I'm trying to do is calculate the subtotal for 2 line items based on the green and yellow discount tables:

Image of my sheet

This would be a correct calculation for 40 site licenses:

20 * 150 + 10 * 120 + 10 * 90 = 5100

Upvotes: 1

Views: 3306

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34265

The classic approach to this would be

=SUMPRODUCT((C26>H$6:H$12)*(C26-H$6:H$12+1)*(J$6:J$12-n(J$5:J$11)))

Adapted from

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(SUM({QUERY(({0; I$6:I$12}-I$6:I$13)*-1, 
 "limit "&MATCH(VLOOKUP(C6, H$6:H$12, 1, 1), H$6:H$12, 0)-1); 
 C6-VLOOKUP(C6, I$6:I$12, 1, 1)}*
 INDIRECT("J6:J"&MATCH(VLOOKUP(C6, H$6:H$12, 1, 1), H$6:H$12, 0)+ROW(H$6)-1)))

0

spreadsheet demo

Upvotes: 1

Related Questions