SUMPRODUCT Formula Format: Important Note   

 Back (Page 4)   Excel Review Home

 

This is the formula we've been working with:

 

=SUMPRODUCT($C$4:$H$4,C9:H9)

 

Notice that in this SUMPRODUCT formula I've used absolute addressing for the reference to Qty Produced, that is cells C4, D4, E4, G4, and H4 (written in the formula as $C$4:$H$4).

Using absolute addressing for this part of the formula allows me to properly copy the SUMPRODUCT function for long dowel down the column for the other ten chair components: short dowel through spanish rail.

However, in the SUMPRODUCT formula, I use Excel's relative addressing default in the part of the formula (C9:H9) that refers to the long dowels row. When I copy down the Total Usage column, I want this relative row reference to automatically change to refer to the correct row for each of the other components.

There is an alternative to using absolute and relative addressing to construct the first formula and then copying that formula down the columns. The alternative is to enter ten separate formulas.

If you're not familiar with Excel's relative and absolute addressing, brush up on this feature in Spreadsheets: Getting Started and/or A Quick Review of Excel Essentials. Relative and absolute addressing are important only when you're copying formulas, but you're likely to copy formulas quite a bit if you do much model building.