HomeApplicationOffice information

How to use SUMPRODUCT function in Excel

One of the most commonly used Excel functions when calculating on Excel is the SUMPRODUCT function. SUMPRODUCT function in Excel is used to calculate the number of numerical elements in an array or different arrays, different from the PRODUCT used to calculate the product of values.

The array on Excel will be a series of data with consecutive values ​​that the user localizes on the data table. SUMPRODUCT functions are applicable on all Excel versions, with the same usage methods. How to use the SUMPRODUCT function details in Excel will be guided by Network Administrator in the article below.

SUMPRODUCT function syntax is = SUMPRODUCT (array1, [array2], [array3], .) .

Inside:

  1. Array1: The first array argument that the user wants to multiply and then calculate the sum. This argument is required.
  2. Array2, Array3: are array arguments that can be optional with or without, in the range from 2 to 255.

The arguments in each array must have the same size, the same number of elements. In the case of elements in non-numeric arrays, SUMPRODUCT function will automatically assume it is zero.

We will calculate the amount of book sales of a store in the example table below. In particular, the table has given the number of each type of book and the price of each type calculated in thousand dong units.

How to use SUMPRODUCT function in Excel Picture 1

We will enter the formula to calculate = SUMPRODUCT (C2: C4, D2: D4) and press Enter to perform the calculation formula.

How to use SUMPRODUCT function in Excel Picture 2

Soon the user will have the store's revenue results when selling the total amount of books given above.

How to use SUMPRODUCT function in Excel Picture 3

So you already know how to use the SUMPRODUCT function to sum the products on Excel, using the example above. Users can calculate the sum of many different arrays in the data table, but the objects in an array must have the same size to avoid reporting the Value error when calculating the result.

I wish you all success!

5 ★ | 1 Vote
excel calculation methodexcel functionsexcel tips

You should read it

May be interested

  • How to use MID functions to get strings in ExcelHow to use MID functions to get strings in Excel
    mid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
  • How to use Excel's VALUE functionHow to use Excel's VALUE function
    excel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
  • How to use the WRAPROWS function in ExcelHow to use the WRAPROWS function in Excel
    having trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
  • How to use the IFS function in Excel 2016How to use the IFS function in Excel 2016
    the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
  • How to use the NPER function in Excel to plan loans and savingsHow to use the NPER function in Excel to plan loans and savings
    do you want to effectively manage and control your personal finances? then we invite you to learn how to use excel's nper function.
  • How to use the function to delete spaces in ExcelHow to use the function to delete spaces in Excel
    deleting white space with functions in excel makes it easier for users to handle content, instead of traditional editing.
  • How to fix the SUM function doesn't add up in ExcelHow to fix the SUM function doesn't add up in Excel
    in the process of summing with sum in excel, you will encounter some errors such as not jumping the number, not adding the sum. so how to handle this problem?
  • How to use the SUBTOTAL function in ExcelHow to use the SUBTOTAL function in Excel
    the subtotal function in excel is used in many different cases, helping you to sum subtotals in a list or database, unlike the sum function in excel such as counting cells, calculating average, finding the largest / smallest value. or sum the filtered list values ​​in excel
  • Save time with these text formatting functions in Microsoft ExcelSave time with these text formatting functions in Microsoft Excel
    microsoft excel is a main application for anyone who has to work with numbers, from students to accountants. but its usefulness extends beyond a large database, it can do a lot of great things with text. the functions listed below will help you analyze, edit, convert, change text and save many hours of boring and repetitive tasks.
  • How to use the SUMIF function in ExcelHow to use the SUMIF function in Excel
    the sumif function in excel is a function used to compute values ​​in a specified range. the sumif function can be used for summing cells based on the date, data and text that are connected to the specified area.

Office information