Learn how to use the SOMARPRODUCT function of Excel

42
29475
Learn how to use the SOMARPRODUCT function of Excel
Excel Spreadsheets

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:

Learn how to use the SOMARPRODUCT function of Excel

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:

Learn how to use the SOMARPRODUCT function of Excel

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.

Learn how to use the SOMARPRODUCT function of Excel

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:

Learn how to use the SOMARPRODUCT function of Excel

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:

Learn how to use the SOMARPRODUCT function of Excel

Thus, we get the result the total amount spent with all these professionals in a month of work. Look the following picture:

Learn how to use the SOMARPRODUCT function of Excel

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!

Excel Spreadsheets

42 COMMENTS

  1. Hello
    I have a spreadsheet that describes QUANTITY / SERVICES / MATERIAL.
    The sum of services and material gives a value, which is multiplied by the value of the quantity gives the total value of everything in the note. I would like to know, how can I identify the exact value of services and materials.

  2. Hi Rafael.
    Can you help me.
    I have to do this activity but I couldn't understand.

    The orientation is as follows:

    Create a table with 10 students with the data presented in Figure 5.1.

    1. Calculate the students' final grade according to their weight using the Add Product function. Remember that we can create a formula with functions, that is, you will need to add the value of more than one function Add Product.

    2. Include a column indicating whether the student is approved or failed. To be approved, the student's grade must be higher than 6,0. Use the IF function.

    3. Using the cont function, present the number of approved students.

  3. I need to obtain a formula from the following example:
    Pack - 02/02 - R $ 50,00
    Pack - 08/02 - R $ 50,00
    Pack - 02/04 - R $ 50,00
    Pack - 02/05 - R $ 50,00

    That is, I need the values ​​added up per month and posted in the respective cells.
    Would it be SOMASE or SOMARPRODUCT?

  4. Good afternoon! The formula below helps to find several results (pulling the selection of the initial result down) based on a criterion. I believe that the same formula can be used with more than one criterion to obtain more than one result. I've assembled it in some ways but so far I haven't been able to assemble it in a way that can use more than one criterion. Would you like to help me?

    =SE(LINS($1:1)>CONT.SE($B$2:$B$21;$F$2);””;ÍNDICE($A$2:$A$21;MENOR(SE($B$2:$B$21=$F$2;LIN($B$2:$B$21)-LIN(B$2)+1;FALSO);LINS($1:1))))

  5. Hi Edson, I don't know what it can be. Another way might be to create an auxiliary column with formulas calculating what the SOMARPRODUCT does (not the best way, but if you don't understand the function it might be a solution).

  6. Hi Rafael. I have a problem using the SOMARPRODUCT formula. I am preparing a spreadsheet to calculate the cost of services, where in the first column are the names of the products / inputs, in the second column are the unit values ​​of these products / inputs and the subsequent columns are the services. In each service column, I type a quantity of products / inputs used in each service. At the bottom of each service, I have to calculate its total cost, which is the sum of the multiplications between the quantity of products / inputs by their respective unit value. The ideal solution for finding these total costs is by using the SOMARPRODUCT formula, but the results are not showing, the cells are clean. I am suspecting that the problem is being caused by the second column, that of unit values, because these values ​​are the result of using the PROCV formula. When I turn the result of the PROCV formula into numbers, the results appear. I did not understand why the SOMARPRODUCT formula is not recognizing PROCV values. Could you explain? Would there be a solution for these SOMARPRODUTO results to appear without turning unit values ​​into numbers?
    Thank you!!!!

  7. Hi Gabriel, I could not visualize exactly what you need, but maybe the SE function tied to others can give you the desired result

  8. I need a function that compares the 1 project number and throws the executed values ​​according to the reference month.
    I had managed to use the method somarproducto, but with the data update, it changes everything and I could not use it anymore.
    Is there any formula for me to be able to change that?
    Thank you.

  9. Hi Carlos, how are you? In this case you can use CONT.SE as a way of counting the values ​​in a conditioned way to the item next to you

  10. Hi Carlos, you can put the items to the right side of the values ​​and use a PROCV with GREATER to show the item attached to the highest value

  11. Hi, Rafael, thanks for the explanation, great learning space!
    Seizing the opportunity, could you help me with a situation, please? I came across a problemon assembling a spreadsheet. I need the formula to continuously check a range of rows and return the highest value, but maintaining the proper relationships between values ​​and subjects. In summary:

    Name ===== Value Avulso
    X =========== 2
    Y =========== 3
    Z =========== 3
    Z =========== 1
    X =========== 2
    Y =========== 5
    X =========== 7
    Z =========== 5

    Highest value corresponds to the letter:?

    They are many values ​​and it is a PivotTable, which will continuously receive new values. Could you tell me the best formula to make this work?

    Thank you very much in advance, strong hug and sorry for the duplicate post. I only realized the mistake after I sent it.

  12. Hi, Rafael, thanks for the explanation, great learning space!
    Seizing the opportunity, could you help me with a situation, please? I came across a problemon assembling a spreadsheet. I need the formula to continuously check a range of lines and add up all values ​​that relate to the same subject. In summary:

    Name Value Avulso
    X 2
    Y 3
    Z 3
    Z 1
    X 2
    Y 5
    X 7
    Z 5

    Sum of Values
    X:?
    Y:?
    Z:?

    They are many values ​​and it is a PivotTable, which will continuously receive new values. Could you tell me the best formula to make this work?

    Thank you very much in advance, strong hug!

  13. Hi Roger, I did not quite understand your question, but basically SOMAPRODUTO multiplies each item in the 1 array by the corresponding items in the 2 array

  14. They provided me with a solution similar to WESLEY YOURI DA SILVA, in a question that I created in a well-known Excel forum.

    Although I am now understanding the logic, I still do not understand WHERE this logic came from, because it comes completely from the syntax of the SOMARPRODUCT function, where the arrays are separated by a comma point, and then sum the products of those arrays.

    Can you point to text / article explaining the creation of arrays from [fieldXLUMX] * [field1] (equal, greater, smaller, etc) condition ???

  15. Hi Charles, the SUBTOTAL function has some possibilities like average, sum, count, etc., but the somarproduct is not one of these possibilities

  16. The formula SOMARPRODUCT is for many cases, the only problem I face using it is that there is no equivalent function inside VBA, I use SOMARPRODUCT practically in all my spreadsheets, this formula serves to make sum according to established criteria within of the formula itself, for example: we have a column [month], another [name] and another [value], and we would have to know the sum of [value] for the criterion [month] and [name] what is the sum of [value] for John in January, in a table created with the columns quoted above, you will need to create a table where the months will be the columns, the names will be the rows, and the cells that cross months and names will be the values. in general the structure of the formula would look like this:

    = SUMPRODUCT (([COLUMN MONTH] = [TEXT OR searched value MEs]) * ([Column Name] = [value, or searched text to Name]) * ([column value]))

  17. Hi Leandro, I do not quite understand, but it seems to me that you can create auxiliary columns to make SOMARPRODUCT directly in them

  18. Good evening, Rafael.

    I'm having a question, I used "SOMARPRODUCT" to add up monthly sales and highlight the value in a separate place month by month, right, it worked, it picks up sales for a given month and adds up next to the specific month, however, I tried do this with the commission column, (separate also, in order to have the result of commissions added month to month) the commission column is the result of the sale (day) with the% of the commission, however, is not showing the result, in the theory would be the same formula, (= SOMARPRODUCT ((MONTH ($ D $ 2: $ D $ 1000) ** month of sale ** = J2 ** reference month **) * ($ E $ 2: $ E $ 1000 ** sales value **))) however, it only displays #VALUE !. OBS: I can not add just the amount of the commission directly on the result of the monthly sales because I have varied commissions for each type of sale. Did you understand this dilemma? if so, is there anything I can do differently to get the result? I do not know much about excel and I did not find anything like it. Thank you, sorry for the text.

  19. Hi Carlos, for the result if updating according to the filter used it is necessary to use SUBTOTAL

  20. Good afternoon
    I applied the sum product in my table, but when I use filters the result is not updated ??? What should I do to make the sum product update when I use filters (ie display the result of visible lines)

  21. Hi Cristiano, because you do not create an auxiliary column that only multiplies the values ​​if column C is 1. Then just add the values ​​from this column

  22. Good morning
    I need to multiply a column that has the quantity by the column that has the values ​​but having as a reference a criterion ... I'm mounting the formula as follows: = SOMARPRODUCT (D5: D5000 * E5: E5000 * (C5: C5000 = "1")) ) but the value is zeroed. Column D represents the quantity, E represents the values ​​and C the criteria. I would like to focus only multiplied the values ​​corresponding to the "1" criterion, how do I?

  23. Hi Kelli, I did not quite understand what you meant. Can you explain a little more?

  24. Good night!
    The following example follows:
    I have 3 function codes and sub function equal and different values ​​for each row, how to group in a row only?
    Coding function = 1000 (first column); second column sub function = 1, these codes will repeat on 3 lines, only with different values. I await an answer. Thanks

  25. Hi Suellen, I think you can create auxiliary columns with SE that show the values ​​whenever x is equal to the criterion, then it is only SOMARPRODUCT of the auxiliary columns

  26. I think it can not correct because the formula is to do the sum of products, not to bring text - to bring text you can make simple references, use PROCV, INDEX with CORRESP, etc.

  27. Hello,
    I need to do a conditional someting product. How do I do?
    Example: I have the x and z columns. I want to add the product of the yez column, if x is equal to the criteria I choose.
    Thanks in advance!

  28. I did not understand what you mean, Marcio. Excel does not do automatic multiplications unless you write a function or formula for it. Can it be a cell formatting problem that causes a change in its appearance and makes it appear that a multiplication has occurred?

  29. Good morning,

    Great article. Helped me a lot!! I was having difficulty exactly in automating the multiplication and its sum and that solved my problem.

    Thinking about variations of this function, is it possible to multiply between rows and columns or just between columns?

    I thank you for your attention.

  30. Good afternoon,
    I'd like some information, please. I type a small number in excel, such as 2,7822. Automatically it multiplies this number by 1000. If I type 2,7 or 2,78 all right. But time I type 2,782 it already multiplies. Would anyone know what's going on ???
    No more, thank you.
    Marcio

  31. Hi Juliano, I do not know if I understood very well, if you can explain a little more I can try to understand and help. In the mean time, I suggest you put this doubt there in our forum - https://forum.luz.vc/

  32. Good day.
    In a listing where I have multiple codes that are repeated in the column and in the other column different values ​​using the other product it does not bring me the values ​​in another worksheet that has the same codes repeated. How can I fix this?

  33. Instead of using SOMARPRODUCT you can simply multiply the fields and then add the column of that result. For example, if in A1 you have the quantity and in B1 you have the values, you can put in C1 a simple A1 * B1 mulitplication. Then just add column C

  34. I use the add product function in a spreadsheet that lists me the products that came out per month, but my list of products is approximately 500 items and reached a point where the worksheet got super slow, would it help me to use another way to do it ?!

LEAVE AN ANSWER

Please, write your comment
Please enter your name