When you work in Excel spreadsheets, you might find yourself working with multiple variables. For example, you may have purchased a variety of items in different quantities and prices. It’s useful, with a single simple formula, to calculate the totals. Fortunately, Excel can do this for you easily. Let’s learn how to use SUMPRODUCT in Microsoft Excel.
How to Use SUMPRODUCT in Excel
The SUMPRODUCT function is simple, yet powerful. It finds the sum of a series of products, or multiplied values, that exist in ranges in your spreadsheets. Consider the table example below. It’s a listing of purchases made, and you need to find the total.
Of course, you could multiply A2*B2, A3*B3, then sum up the results. But Excel’s SUMPRODUCT function will do this work for you.
To get started using SUMPRODUCT, click into any empty cell in your workbook. Then, type an = sign. This tells Excel that you’re inputting a formula into the selected cell.
Now, it’s time to insert the SUMPRODUCT function. To do that, type SUMPRODUCT(. Your formula reads:
=SUMPRODUCT(
Next, select the two arrays. Remember, you need the prices in one range, and the quantities in the other. Simply click and drag to select the first range, A2:A6, type a comma, then select B2:B6). Finally, close the parentheses, completing your formula as follows:
=SUMPRODUCT(A2:A6,B2:B6)
Hit Enter on your keyboard, and Excel returns your solution: $7,070.
As you can see, the SUMPRODUCT function is a powerful tool to use inside Excel. It helps you quickly sum up multiple values found within ranges in your workbook.
Rather than repeating multiplication steps and then adding the results, SUMPRODUCT performs all of these calculations automatically.
SUMPRODUCT functions are easy to set up, and they’re sure to save you valuable time. Try them next time you’re adding values in Excel spreadsheets.
Reduce File Size in Excel