Excel Function SUMPRODUCT

SUMPRODUCT function performs multiplication of numbers within arrays and then sum the values

SUMPRODUCT function has one required argument i.e. array1 and optional argument i.e. [array2]… pulvinar dapibus leo.

Excel VBA Master Tool Kit
Syntax:
=SUMPRODUCT(array1,[array2],...
Syntax Description:

array1 argument is used to give range for which you want to multiply and then sum

[array2] is optional argument and is used to give another range for which you want to multiply and then sum

… means, we can add multiple range by separating them with comma ( , )

Example 1: Excel Function SUMPRODUCT with cell range

  • “Column A” shows the array1 argument range,
  • “Column B” shows the array2 argument range,
  • “Column C” shows the sample formula applied and,
  • “Column D” shows the output of the function,
Excel Function SUMPRODUCT
Explanation:

SUMPRODUCT Function will first multiply the values within arrays i.e. 1st value of 1st array, multiply with 1st value of 2nd array (2*1=2)

+ 2nd value of 1st array, multiply with 2nd value of 2nd array (6*5=30)

+ 3rd value of 1st array, multiply with 3rd  value of 2nd array (3*4=12)

and then, Sum all the values i.e. 2+30+12=44

i.e. =A2*B2 + A3*B3 + A4*B4 = 44

Things to Remember:

– If function has only one array (i.e. range) then it will Sum all the values

– Multiple ranges can be applied in function by separating them with comma ( , )

– Text/ Blank values will be considered as zero (i.e. 0)

– If No values in range is provided in array argument, then output will return as 0 (zero)

Recommended Articles

Hope you learnt this Function,

Don’t forget to leave your valuable comments!

If you liked this article and want to learn more similar tricks, please Subscribe us or follow us on Social Media by clicking below buttons:

Similar Posts

Why use dollar sign ($) in Excel formulas – absolute and relative cell references
| |

Why use dollar sign ($) in Excel formulas – absolute and relative cell references

BySumit Shrimal

When creating a formula in Excel, many people get confused by the $ symbol in cell references. But it’s actually very simple. The dollar sign in an Excel cell reference tells Excel whether to keep the reference the same or allow it to change when you copy the formula to other cells. This guide will explain how it works and why it’s helpful.
Understanding Excel cell references is really important. Knowing the difference between absolute, relative, and mixed references will help you get the most out of Excel formulas and functions.

Linear regression analysis in Excel
|

Linear regression analysis in Excel

BySumit Shrimal

The tutorial teaches you the basics of regression analysis and shows a few ways to do linear regression in Excel.
Imagine this: you have a lot of data and need to predict next year’s sales for your company. You’ve found many factors that could affect the sales, maybe even hundreds. But how do you know which ones really matter? You can use regression analysis in Excel. It will help answer this and other questions, like: Which factors are important and which ones don’t matter? How connected are these factors? And how confident can you be in your predictions?”

Excel Hyperlink: how to create, change and remove
| | |

Excel Hyperlink: how to create, change and remove

BySumit Shrimal

This tutorial shows you three easy ways to add hyperlinks in Excel. You will learn how to insert, change, and remove hyperlinks in your worksheets. It also explains how to fix links that don’t work.
Hyperlinks are often used on the internet to move between websites. In Excel, you can create links like that too. You can make a link to another cell, a different sheet, or even another workbook. You can also link to open a new Excel file or start an email message. This guide will show you how to do all of this in Excel 2016, 2013, 2010, and older versions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Excel VBA Master Tool Kit