max value not exceeding 1500

In the cell where you do the =SUM(<yourRange>) for the invoice total, use instead =MIN(1500,SUM(<yourRange>)

More on MIN function here.

SG

If it was me, I would put a line that shows the price discount (as a negative) and include it in the sum. The items on an invoice should always add up to the total, no hidden discounts or fees. Plus I'd want the cost reduction I gave them to be obvious, given that it it is a benefit to them.

As an example, if your itemized list of items is in A10:A20 and the cost for them is in D10:D20,

I would add a new row 21

A21 would be something like "Price Discount"

D21 would be =MIN(0,1500-SUM(D10:D20))

The totaling formula would change from =SUM(D10:D20) to =SUM(D10:D21)