Excel is a great tool to use when calculating costs or even knowing how much you spend on a particular payroll. To perform these calculations, the function SOMARPRODUCT is used, which according to Excel itself, "returns the sum of the products of corresponding intervals or matrices". In other words, this function calculates the sum of partial products.
In today's post we will learn how to use this function through the examples cited above. Come on?
Example of using the SOMARPRODUCT function in the Service Costs worksheet
For this example, imagine that you want to calculate how much you spend on certain services, such as: Security, Cleaning, Marketing, and Attendance (Secretary, in this case). To do this, you will create a worksheet with the number of employees in each area, the amount of time and the number of hours worked in the month. Watch:
When you enter all the data in the spreadsheet, the next step is to use the SOMARPRODUTO function, where it will multiply all the cells of each row and then add up all those values.
To do this, you need to enter the following: = SOMARPRODUCT ((C4: C7) * (D4: D7) * (E4: E7))
The expressions (C4: C7), (D4: D7) and (EXNXX: E4) represent the arrays that were summed and the asterisk (*) sign multiplies these arrays. Observe the following figure:
After entering the function and pressing the understand key, we have as a result the total amount spent, per month, with the services described, according to the following figure.
To make it even clearer, we'll go through another example step by step. Follow us!
Example of the use of the SOMARPRODUCT function in the Payroll of Employees of a particular sector
In this case, we want to calculate the total value of the payroll in the marketing industry. For this, as in the previous example, we need to enter the data of: number of employees, hour value and number of hours worked in the month. Look at the following figure:
After that, the next step is to insert the SOMARPRODUCT function, just as in the previous example. However, in this case, we have only three lines. Thus, we type as follows: = SOMARPRODUCT ((C4: C6) * (D4: D6) * (E4: E6))
The expressions (C4: C6), (D4: D6) and (E4: E6) continue to represent the matrices that were summed and the asterisk (*) sign multiplies these matrices. Observe the following figure:
Thus, we get the result the total amount spent with all these professionals in a month of work. Look the following picture:
Now that you have understood in which cases we can use the SOMARPRODUCT function, let's understand its concept in Excel. Follow us!
Understanding the SOMARPRODUCT function conceptually in Excel
The SOMARPRODUCT function, as stated above, multiplies the values of the provided matrices and grants the sum value of those products. Its structure has the following composition:
SOMARPRODUCT (matrix1; [matrix2]; [matrix3], ...)
The syntax of the SOMARPRODUTO function has the following arguments:
- Matrix1 Required. This is the first argument of the function and provides the values you want to multiply and then sum.
- Matrix2; matrix3, ... Optional. They are the arguments of the other arrays, which may be between 2 and 255, which has the values you want to multiply and then add.
At this point, it is important to note that the arguments of each matrix must have the same dimension. Otherwise, the SOMARPRODUCT function will not be able to find the value, giving an error expressed by "#VALOR!".
The following is the general step-by-step to use this function. Come on?
Step by step how to use the SOMARPRODUTO function
To use this function, you must first have three columns. The number of rows depends on the amount of data you want to provide. Therefore, we have:
- Step 1 - Mount the worksheet according to the data you want to get;
- Step 2 - Enter the SOMARPRODUCT function according to the examples described at the beginning of this post;
- Step 3 - Find the desired value.
As you might have noticed, this function is easy to use because its comprehension is extremely simple. Just follow the steps taught here and apply them to your business worksheets. Good job!