How do I change the value in a cell based on the total sum of a different sequence of cells?

Pending answers to Barry's questions I've made some assumptions to give you an idea of one way to approach the problem:

The formula in D2:

=INDEX({8.5;8.25;8.1;8},MATCH((A2+B2+C2),{0;12;24;36},1))

This totals the quantities in A2, B2, and C2 and looks up where that total is in the list of the lower ends of your quantity ranges \ {0;12;24;36}. That position is fed to to INDEX, which retrieves the corresponding value from {8.5;8.25;8.1;8}, which lists the prices for each of the quantity ranges.

SG

What price is charged per unit for a quantity less than 12?

Are there further price reductions for quantities greater that 35?

Does the 'best price apply to ALL the items or to just the items in the specified range?

The formula for counting/totalling the pieces will depend on the actual location of the cells containing that information (Numbers and identification of the pieces). If the cells holding the numbers are not reserved exclusively for counting that specific piece.

A more detailed description of what your document looks like will help in determining a solution to fit. A screen shot of the design would also be useful.

Regards,

Barry