Thursday, November 04, 2004

Summing two columns together in Excel

Have you ever needed to sum the results of two columns together. A common examplw would be a list of stock items and then quantity and unit cost.

Say you have the following three columns; A - Description, B - Quantity, C - Unit Cost.
Most people would create a fourth column for the value for each item being the Quantity times the Unit cost. They would then total this column for the total inventory value.

Well there are several other ways of doing it. (assume data in rows 2-11)

Use this normal formula =SUMPRODUCT(B2:B11,C2:C11)

Use an array formula (Press CTRL SHIFT ENTER when entering or editing the formula (CSE)) {=SUM(A2:A11*B2:B11)}
Note the {} come only after entering using CSE

Notes:
Both formulas will work across different sheets.
The CSE forumla will not work with a range of an entire row or column.


Posted by: Jethro | 11:09 PM |