Calculate the value of your inventory in one step with this handy Excel function

Why use two formulas when one will do the trick? See how you can add and multiply values in one fell swoop with Excel's SUMPRODUCT function.

Let's say your company keeps a record of its inventory in the worksheet shown in Figure A.

Figure A

You need to calculate the total value of the inventory-on-hand from this worksheet. Your first instinct may be to create a new column that multiplies the cost of each item by its quantity and then sum the values obtained. An alternative way is to use Excel's SUMPRODUCT function, which adds and multiplies in one step. Using this example, click in G2 and enter the following formula, as shown in Figure B:

Figure B

SUMPRODUCT multiples the values in the range B2:B11 by the values in C2:C11 and then returns the sum of those products.

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox