What does it do?

It returns the sum of the products of corresponding ranges or arrays

Formula breakdown:

=SUMPRODUCT(array1, [array2], [array3]…)

What it means:

=SUMPRODUCT(this array, with that array…)


A quick way to calculate the weighted average of two lists of data is to use the SUMPRODUCT formula.  A weighted average can be used to determine the average salary of employees, the average grade of an exam or the average selling price of a company´s stock list, as can been seen below.

DOWNLOAD EXCEL WORKBOOK

We want to get the average selling price of our total stock items. This is easily achievable with the SUMPRODUCT formula! We will use this to calculate the total value of the items, then divide this by the total number of units to get the average selling price.

STEP 1: We need to enter the SUMPRODUCT function in a blank cell:

=SUMPRODUCT(

SumProduct Formula

 

STEP 2: The SUMPRODUCT arguments:

Array1

What is the first array that contains the data?

We want to get the units sold so select those values.

=SUMPRODUCT(C14:C17,

SumProduct Formula

Array2

What is the second array that contains the data?

We want to get the sale price sold so select those values. The values will be multiplied against the first array that we got.

=SUMPRODUCT(C14:C17, D14:D17)

SumProduct Formula

 

STEP 3: Now we have the total value, we can easily get the average value by dividing by the total number of items.

=SUMPRODUCT(C14:C17, D14:D17) / SUM(C14:C17)

SumProduct Formula

With just this single formula, we are able to get the average selling price without the need of extra helper columns!

SumProduct Formula

How to Use the Sumproduct Formula in Excel

Sumproduct_Easy

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Vlookup in Multiple Excel Sheets What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another sheet´s column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLO...
VLOOKUP Multiple Columns What does it do? Searches for a value in the first column of a table array and returns the sum of values in the same row from other columns (to the right) in the table array. Formula breakdown: {=SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, ))}...
Match Two Lists With The MATCH Function I am sure that you have come across many occasions where you have two lists of data and want to know if a specific item in List1 exists in List2. Well I have! With the MATCH function you can verify if a cell´s item in List1 exists in List2. The function will return the r...
Create a Dynamic Data Range with the OFFSET functi... What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number o...