Further on in this tutorial, you will find a handful of formulas that demonstrate more advanced and exciting uses, so please keep reading. The real beauty of the Excel SUMPRODUCT function is that it can do far more than its stated purpose. Multiplying two or more ranges together and then summing the products is the simplest and most obvious usage of SUBTOTAL in Excel, though not by far the only one. How to use SUMPRODUCT in Excel - formula examples If you want to only multiply the numbers in each row without adding up the products, then use one of the formulas to multiply columns in Excel. Just think how much time it could save you if your table contained not 3 rows of data, but 3 hundred or 3 thousand rows! In other words, our SUMPRODUCT formula performs the following mathematical operations: When all of the array elements are multiplied, the formula adds up the products and returns the sum.The formula takes the 1 st number in the 1 st array and multiplies it by the 1 st number in the 2 nd array, then takes the 2 nd number in the 1 st array and multiplies it by the 2 nd number in the 2 nd array, and so on.Here is what's going on under the hood in terms of math: The following screenshots shows it in action: In Microsoft Excel, you can get the result with a single SUMPRODUCT formula: If you were doing a school math test, you would multiply the quantity by price for each item, and then add up the subtotals. Supposing you have quantity in cells A2:A4, prices in cells B2:B4, and you wish to find out the total. To gain a general understanding of how the Excel SUMPRODUCT function works, consider the following example.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |