The days when accountants used to calculate payroll using a hand-held account or a calculator with a minimal set of functions and huge buttons have sunk into oblivion. To date, all economic and accounting calculations are made using software, there is a lot of specialized software that allows you to work automatically. But sometimes it becomes necessary to perform some calculations that are not provided by the software used by the employer. Then the familiar Microsoft Office tools come to the rescue.
Microsoft Excel is one of the main tools for working with numbers and arrays of numbers, for sorting and selecting records, analyzing data, drawing up plans and charts. This package has a number of built-in functions that allow you to perform calculations in automatic mode. Functions allow you to greatly simplify the formulas that are used in an Excel spreadsheet, so that the document is not overloaded with long formulas and calculations. For a complete list of built-in functions in Excel, see Microsoft Office Help.
One of the many useful functions that help you work with multidimensional data arrays and matrix calculations in Excel is the sum of products.
How can I use built-in functions in Excel?
The built-in functions in Excel expand the main features of the system, and in order to view the entire list of these features, you can simply use the fx icon located in front of the formula bar (a white line in which characters are typed when filling cells in Excel).
After clicking the fx icon, the Function Wizard window will appear. Following his prompts, you can select the appropriate function and get brief information on how to work with it. In this case, you can use several options for searching for a function:
- Apply a filter and set the category of the function to make it easier to find the necessary formula that will allow you to make the necessary calculations. The sum of products in Excel is included in the Math category.
- Use search by name (this option is only suitable if you know the name of the desired function in Excel). In the "Search for a function" window, you must type the name and click the "Search" button,the system itself will find the names of the built-in functions that match the entered query. In order to calculate the sum of products of numbers from given arrays, in the search box, type the name of the function - "SUMPRODUCT".
To use a built-in function, select it with the cursor and press the OK button in the "Function Wizard" window. After completing these steps, the "Function Arguments" window will appear, in which you will need to specify all the required parameters.
If the name of the function is already known, and the user does not need additional hints on its use, you can simply put an equal sign (=) in the formula bar and enter the name of the built-in function. At the same time, it must be remembered that in addition to the name, you will also need to enter the arguments of the given Excel function.
Function arguments for calculating the sum of products
The function for calculating the sum of products of numbers from given arrays determines the result of performing certain actions with values that are used as function arguments. In this case, values of various types can act as arguments, and the system will prompt the user for the required one. The Sum of Products formula in Excel multiplies the corresponding values from the specified arrays (using specific rows or columns as arrays) and returns the sum of the products of the values given in the arrays.
Function syntax and delimiters
In order for built-in functions to work correctly, you should strictly follow the established for themsyntax (that is, use standard delimiters between function arguments).
The function for calculating the sum of products has the following syntax - "SUMPRODUCT(array1, array2, array3, …)".
That is, brackets and commas should be used for correct calculations, and a colon (:) should be used as a separator to specify start and end cells with arrays. The arguments can be values that provide information for an event, action, method, property, function, or procedure. Array 1 is a required argument for the above function, and subsequent arrays are specified as needed. The number of optional arrays (whose components will be multiplied for further summation) can be from 2 to 255.
Example of using a function to calculate the sum of products
To illustrate the use of the SUMPRODUCT function, consider the following example:
- Fill in the cells in the Excel table as shown in the figure below.
- In the formula bar, write "SUMPRODUCT(A2:B5;C2:D5)". In order to start typing a function into a cell, you should put an equal sign in it, and then write down the correct name of the built-in function and all the necessary arguments.
- Press the Enter key.
At the same time, in the cell for which the formula was typed, Excel will display the result of the calculation. In this case, it will be the number 9290. The system performed the followingarithmetic operations:
1×100+3×500+5×230+6×250+2×250+4×130+9×400+2×210=9290.
Errors when using the function
The most common mistakes when using Excel's built-in functions are:
- Using incorrect syntax (an extra space between characters or a comma instead of a colon can lead to errors in the function).
- Arguments used as arrays in the specified function must have the same dimension. If you do not follow this advice, the function will not work correctly and will return the error value (VALUE!) to the cell.
- If non-numeric elements are used as values in arrays, then they are perceived by the SUMPRODUCT function as zero and may lead to an incorrect final calculation result.
Do not be afraid to use the capabilities of Microsoft Excel to the maximum, and then it will become your reliable assistant in routine work! It seems to many users that they will never need to calculate the sum of products in Excel, but when they start using the program's capabilities in their work, this functionality turns out to be quite useful.
