The times when accountants calculated wages using a manual account or a calculator with a minimal set of functions and huge buttons disappeared into oblivion. Today, all economic and accounting calculations are performed using software, there are many specialized software that allows you to perform work in automatic mode. 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, making plans and diagrams. The specified package has a number of built-in functions that allow you to perform calculations in automatic mode. Functions can significantly simplify the formulas that are used in the Excel spreadsheet so that the document is not overloaded with long formulas and calculations. For a complete list of built-in functions in Excel, use 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 the pieces.
How can I use the built-in functions in Excel?
Built-in functions in Excel expand the basic capabilities of the system, and in order to view the entire list of these features, you can simply use the f x icon located in front of the formula bar (white line in which characters are typed when filling out cells in Excel).
After clicking the f x icon, the “Function Wizards” window appears. Following his prompts, you can select the appropriate function and get brief information on working with it. You can use several options for finding a function:
- Apply a filter and set the function category to facilitate the search for the necessary formula that will allow you to make the necessary calculations. The sum of works in Excel is included in the category "Mathematical".
- Use search by name (this option is only suitable if the name of the required function in Excel is known). In the “Search for functions” window, you must type a 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 the products of numbers from the given arrays, in the search window, type the name of the function - "SUMPRODUCT".
In order to use the built-in function, you must select it with the cursor and press the OK button in the "Function Wizards" window. After performing 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 known, and the user does not need additional tips on its use, you can simply put an equal sign (=) in the formula bar and enter the name of the built-in function. It should be remembered that in addition to the name, you will also need to enter the arguments of the specified Excel function.
Arguments of a function to calculate the sum of products
The function for calculating the sum of the products of numbers from given arrays determines the result of certain actions with values that are used as arguments to the function. Moreover, various types of values can act as arguments, and the system will tell the user what is needed. The formula of the sum of products in Excel multiplies the corresponding values from the specified arrays (certain rows or columns are used as arrays) and returns the sum of the products of the values given in the arrays.
Function Syntax and Separators
In order for the built-in functions to work correctly, the syntax set for them should be strictly observed (that is, use standard signs of separation between function arguments).
The function for calculating the sum of the works has the following syntax - "SUMPRODUCT (array1, array2, array3, ...)".
That is, for correct calculations, parentheses and commas should be used, and a colon (:) should be used as a separator to indicate the start and end cells with arrays. In this case, values that provide information for an event, action, method, property, function, or procedure can be arguments. Array 1 is a required argument for the above function, and subsequent arrays are set based on necessity. The number of optional arrays (the components of which will be multiplied for further summation) can be from 2 to 255.
An example of using a function to calculate the sum of products
To illustrate the use of the SUMPRODUCT function, the following example should be considered:
- Fill the cells in the Excel table as shown in the figure below.
- In the line of formulas we write “SUMPRODUCT (A2: B5; C2: D5)”. In order to start entering any function into the 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 (Enter).
In this case, in the cell for which the formula was typed, Excel will display the result of the calculations. In this case, it will be the number 9290. The system at the same time performed the following arithmetic 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 errors when using the functions built into Excel are:
- Using incorrect syntax (an extra space between characters or a comma instead of a colon can lead to errors in the function).
- Arguments that are 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 can 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 amount of works in Excel, but when they begin to use the capabilities of the program in their work, this functionality is quite useful.