{"id":30503,"date":"2023-08-21T19:15:43","date_gmt":"2023-08-21T17:15:43","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=30503"},"modified":"2024-03-22T18:14:25","modified_gmt":"2024-03-22T17:14:25","slug":"sumproduct-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-in-excel\/","title":{"rendered":"3 Examples to Master SUMPRODUCT in Excel"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/870D9618-29B2-4EAE-916C-DFC547B0CCC6-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-30564\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/870D9618-29B2-4EAE-916C-DFC547B0CCC6-1-1024x576.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/870D9618-29B2-4EAE-916C-DFC547B0CCC6-1-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/870D9618-29B2-4EAE-916C-DFC547B0CCC6-1-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/870D9618-29B2-4EAE-916C-DFC547B0CCC6-1-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/870D9618-29B2-4EAE-916C-DFC547B0CCC6-1.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>You might be familiar with the essential function like SUM and PRODUCT in Excel, but there is also a <strong>remarkable function called SUMPRODUCT<\/strong>. The SUMPRODUCT function in Excel is a <strong>versatile tool that empowers users to easily perform complex calculations<\/strong>.<\/p>\n<p>SUMPRODUCT in Excel returns the <strong>sum of the products of corresponding ranges<\/strong> or arrays. It can be used to calculate the <strong>mean salary of employees, the average score on an exam, or the mean selling price of a company&#8217;s inventory, total investment value<\/strong>, etc.<\/p>\n<p>In this article, we will explore the following topics in detail &#8211;<\/p>\n<p>Download the Excel Workbook below to follow along and understand how to use SUMPRODUCT in Excel \u2013 <a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">SUMPRODUCT-in-Excel.xlsx<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"1\" style=\"color: #ff0000;\">Introduction to SUMPRODUCT in Excel<\/span><\/strong><\/h3>\n<p>The\u00a0<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-weighted-averages\/\" target=\"_blank\" rel=\"nofollow noopener\"><b>SUMPRODUCT<\/b> <\/a>in Excel is a versatile tool that performs the task of calculating the total sum derived from the products of corresponding components within given ranges or arrays. The syntax of SUMPRODUCT in Excel is &#8211;<\/p>\n<p><strong>=SUMPRODUCT(array1, [array2], [array3], &#8230;)<\/strong><\/p>\n<ul>\n<li><strong>array1<\/strong> -The first array argument whose components you want to multiply and then add. Required.<\/li>\n<li><strong>array2, array3, &#8230;<\/strong>\u00a0&#8211; Array arguments 2 to 255 whose components you want to multiply and then add. Optional.<\/li>\n<\/ul>\n<p><strong>Please Note<\/strong> &#8211; The dimensions of the arrays should be the same or else this function will return an error.<\/p>\n<p>Let us look at an <strong>example<\/strong> to understand better.<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"2\" style=\"color: #ff0000;\">#1 &#8211; Basic Example<\/span><\/strong><\/h3>\n<p>Suppose you have a dataset containing the quantities of different items sold and their respective prices. You want to calculate the total revenue generated from these sales. Here&#8217;s where SUMPRODUCT comes to your rescue.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span><\/strong> Enter the SUMPRODUCT function.<\/p>\n<p><strong>=SUMPRODUCT<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30506\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-1.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"810\" height=\"330\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-1.png 810w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-1-300x122.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-1-768x313.png 768w\" sizes=\"(max-width: 810px) 100vw, 810px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2:<\/span><\/strong> Enter the first argument i.e. array1. Here, it is the range containing the quantity of items sold (B2:B9).<\/p>\n<p><strong>=SUMPRODUCT(B2:B9<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-2.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30507\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-2.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"830\" height=\"347\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-2.png 830w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-2-300x125.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-2-768x321.png 768w\" sizes=\"(max-width: 830px) 100vw, 830px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span><\/strong> Enter the second argument i.e. array 2. Here, it is the range containing the unit price of the items sold (C2:C9).<\/p>\n<p><strong>=SUMPRODUCT(B2:B9,C2:C9)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-3.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30508\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-3.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"805\" height=\"336\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-3.png 805w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-3-300x125.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-3-768x321.png 768w\" sizes=\"(max-width: 805px) 100vw, 805px\" \/><\/a><\/p>\n<p>Let&#8217;s evaluate this <a class=\"wpil_keyword_link\" title=\"formula\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" data-wpil-keyword-link=\"linked\">formula<\/a> &#8211;<\/p>\n<p><strong>=SUMPRODUCT(B2:9,C2:C9)<\/strong><\/p>\n<p><strong>=SUMPRODUCT({300;250;150;200;100;50;80;70},{10;5;20;30;15;50;25;40})\u00a0<\/strong><\/p>\n<p><strong>=SUMPRODUCT({3000,1250,3000,6000,1500,2500,2000,2800})<\/strong><\/p>\n<p><strong>=22050<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-4.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30509\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-4.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"726\" height=\"333\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-4.png 726w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-4-300x138.png 300w\" sizes=\"(max-width: 726px) 100vw, 726px\" \/><\/a><\/p>\n<p>Excel will first multiply each item in array1 with the corresponding item in array2 and then simply sum the result. The SUMPRODUCT function simplifies what would otherwise involve multiple steps, making it a valuable asset for straightforward calculations.<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"3\" style=\"color: #ff0000;\">#2 &#8211; SUMPRODUCT with other Arithmetic Operators<\/span><\/strong><\/h3>\n<p>While<strong> multiplication is the default operation<\/strong>, <strong>addition, subtraction, and division can also be applied<\/strong>. Utilize SUMPRODUCT in the usual manner, but <strong>instead of using commas<\/strong> to separate array arguments, <strong>use the desired arithmetic operators<\/strong> (*, \/, +, -). For example, to use division with 2 arrays &#8211;<\/p>\n<p><strong>=SUMPRODUCT(array1 \/ array2)<\/strong><\/p>\n<p>Suppose, we have a dataset with the <strong>total sales amount and unit process of different items sold<\/strong>. We want to know the total number of items sold based on the data provided. To do this, we need to <strong>first divide the total sales amount by the unit price for each item and then simply add the result<\/strong> to get the total quantity sold.<\/p>\n<p>Instead, we can also <strong>use the SUMPRODUCT<\/strong> function by replacing the comma with the division sign to get the result &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span><\/strong> Enter the SUMPRODUCT function.<\/p>\n<p><strong>=SUMPRODUCT<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-5.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-30510 size-full\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-5.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"826\" height=\"339\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-5.png 826w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-5-300x123.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-5-768x315.png 768w\" sizes=\"(max-width: 826px) 100vw, 826px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2:<\/span><\/strong> Enter the first argument i.e. array1. Here, it is the range containing the total sales amount (B2:B9).<\/p>\n<p><strong>=SUMPRODUCT(B2:B9<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-6.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-30511 size-full\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-6.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"900\" height=\"349\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-6.png 900w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-6-300x116.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-6-768x298.png 768w\" sizes=\"(max-width: 900px) 100vw, 900px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span><\/strong> Enter the division operator (\/).<\/p>\n<p><strong>=SUMPRODUCT(B2:B9\/<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-7.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30512\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-7.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"904\" height=\"332\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-7.png 904w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-7-300x110.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-7-768x282.png 768w\" sizes=\"(max-width: 904px) 100vw, 904px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4:<\/span><\/strong> Enter the second argument i.e. array 2. Here, it is the range containing the unit price of the items sold (C2:C9).<\/p>\n<p><strong>=SUMPRODUCT(B2:B9\/C2:C9)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-8.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30513\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-8.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"824\" height=\"333\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-8.png 824w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-8-300x121.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-8-768x310.png 768w\" sizes=\"(max-width: 824px) 100vw, 824px\" \/><\/a><\/p>\n<p><strong>=SUMPRODUCT(B2:B9\/C2:C9)<\/strong><\/p>\n<p><strong>=SUMPRODUCT({3000;1250;3000;6000;1500;2500;2000;2800}\/{10;5;20;30;15;50;25;40})<\/strong><\/p>\n<p><strong>=SUMPRODUCT({300,250,150,200,100,50,80,70})<\/strong><\/p>\n<p><strong>=1200<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-9.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30514\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-9.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"821\" height=\"330\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-9.png 821w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-9-300x121.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-9-768x309.png 768w\" sizes=\"(max-width: 821px) 100vw, 821px\" \/><\/a><\/p>\n<p>Excel returns the total number of units sold by dividing the sales amount by the unit price and then adding the result.<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"4\" style=\"color: #ff0000;\">#3 &#8211; Weighted Averages<\/span><\/strong><\/h3>\n<p>SUMPRODUCT can also be used to calculate weighted averages in different scenarios.<\/p>\n<p>Suppose, you have<strong> invested in a portfolio of stocks<\/strong>, and you want to calculate the<strong> weighted average return<\/strong> for your investments. You have the<strong> total amount invested<\/strong> and <strong>the corresponding returns in percentage for all the stocks<\/strong>. Using the SUMPRODUCT function, you can determine the<strong> overall weighted average return<\/strong> of your portfolio.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-16.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30521\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-16.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"454\" height=\"261\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-16.png 454w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-16-300x172.png 300w\" sizes=\"(max-width: 454px) 100vw, 454px\" \/><\/a><\/p>\n<p>You will have to first use the SUMPRODUCT function to calculate the total return (in $) of the investment by multiplying the amount invested with the return% and then adding the result. Then, you will<strong> divide this by the total investment made<\/strong>\u00a0to get the\u00a0<strong>average return of the portfolio.<\/strong><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span><\/strong> Enter the SUMPRODUCT function.<\/p>\n<p><strong>=SUMPRODUCT<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-10.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30515\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-10.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"943\" height=\"340\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-10.png 943w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-10-300x108.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-10-768x277.png 768w\" sizes=\"(max-width: 943px) 100vw, 943px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2:<\/span><\/strong> Enter the first argument i.e. array1. Here, it is the range containing the amount invested (B2:B8).<\/p>\n<p><strong>=SUMPRODUCT(B2:B8<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-11.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30516\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-11.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"931\" height=\"332\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-11.png 931w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-11-300x107.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-11-768x274.png 768w\" sizes=\"(max-width: 931px) 100vw, 931px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span><\/strong> Enter the second argument i.e. array 2. Here, it is the range containing the return of each investment made (C2:C8).<\/p>\n<p><strong>=SUMPRODUCT(B2:B8,C2:C8)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-12.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30517\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-12.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"854\" height=\"305\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-12.png 854w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-12-300x107.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-12-768x274.png 768w\" sizes=\"(max-width: 854px) 100vw, 854px\" \/><\/a><\/p>\n<p>Now we have the total return, we can easily get the average return (%) by dividing it by the total amount invested.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4:<\/span><\/strong> Enter the division operator (\/) and then the SUM function.<\/p>\n<p><strong>=SUMPRODUCT(B2:B8,C2:C8)\/SUM(<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-13.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30518\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-13.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"849\" height=\"323\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-13.png 849w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-13-300x114.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-13-768x292.png 768w\" sizes=\"(max-width: 849px) 100vw, 849px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5:<\/span><\/strong> Select the range containing the amount invested i.e. B2:B8.<\/p>\n<p><strong>=SUMPRODUCT(B2:B8,C2:C8)\/SUM(B2:B8)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-14.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30519\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-14.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"840\" height=\"312\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-14.png 840w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-14-300x111.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-14-768x285.png 768w\" sizes=\"(max-width: 840px) 100vw, 840px\" \/><\/a><\/p>\n<p>Using the SUMPRODUCT and SUM functions, you can easily calculate weighted averages in any scenario.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-15.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30520\" title=\"3 Examples to Master SUMPRODUCT in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-15.png\" alt=\"3 Examples to Master SUMPRODUCT in Excel\" width=\"850\" height=\"316\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-15.png 850w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-15-300x112.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/08\/SUMPRODUCT-in-Excel-15-768x286.png 768w\" sizes=\"(max-width: 850px) 100vw, 850px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span style=\"color: #ff0000;\">Conclusion<\/span><\/strong><\/h3>\n<p>SUMPRODUCT calculates the sum of products of corresponding elements within given arrays or ranges. This function finds application in various scenarios, including basic calculations like total revenue from sales, complex operations involving different arithmetic operators, and calculating <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/weighted-average-shares-outstanding\/\" target=\"_blank\" rel=\"noopener\">weighted average<\/a>s, such as determining the weighted average return on investments in a diverse portfolio.<\/p>\n<p><strong>Further Learning:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/advanced-sumproduct-function-sum-multiple-criteria\/\" target=\"_blank\" rel=\"noopener\">Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/advanced-sumproduct-function-count\/\" target=\"_blank\" rel=\"noopener\">Advanced SUMPRODUCT Function: Count<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/advanced-sumproduct-function-conditional-date\/\" target=\"_blank\" rel=\"noopener\">Advanced SUMPRODUCT Function: Conditional Date<\/a><\/li>\n<\/ul>\n<p><a href=\"https:\/\/support.microsoft.com\/en-gb\/office\/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e\" target=\"_blank\" rel=\"nofollow noopener\"><strong>Click here<\/strong><\/a> to know all about SUMPRODUCT in Excel!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You might be familiar with the essential function like SUM and PRODUCT in Excel, but there is also a remarkable function called SUMPRODUCT. The SUMPRODUCT function in Excel is a versatile tool that empowers users to easily perform complex calculations. SUMPRODUCT in Excel returns the sum of the products of corresponding ranges or arrays. It [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":30568,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[15,285],"tags":[1325,46,1324],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/30503"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=30503"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/30503\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/30568"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=30503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=30503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=30503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}