EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel SUMPRODUCT Function in Excel
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

SUMPRODUCT Function in Excel

By Pradeep SPradeep S

SUMPRODUCT Function in Excel

SUMPRODUCT in Excel (Table of Contents)

  • SUMPRODUCT in Excel
  • SUMPRODUCT Formula in Excel
  • How to Use SUMPRODUCT Function in Excel?

SUMPRODUCT in Excel

Sumproduct function in excel is used when we have 2 or more sets of values in the form of a table, and we need to calculate the multiplication or product of those numbers; simultaneously, we need to find the sum of those values. This complete process can be done by applying the Sumproduct function, which will give the sum and product of the selected array at the same time in a cell. It returns the sum of the products of arrays or corresponding ranges.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

 i.e. It multiplies the range of array or corresponding cells and returns the sum of a products

SUMPRODUCT Formula in Excel:

The SUMPRODUCT Formula in Excel is as follows:

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,858 ratings)

 SUMPRODUCT Formula

Here the first array, i.e. array1, is a compulsory argument or parameter and the remaining n numbers of arrays as an optional argument, i.e. [array2],[array3],…)

Compulsory argument:

  • array1: is the first range or array that you want to multiply and then add.

Optional argument:

  • [array2]: The second range or array will be multiplied then added.
  • [array3]: From the third argument onwards range of arrays will be multiplied & then added. 

Note:

  • The maximum number of arrays that can be entered is 255 &
  • A minimum number of arrays should be 2 in the sum-product function.
  • Square brackets always represent the optional arguments.
  • Array or Ranges of cells must have the same number of columns or rows. 

How to Use SUMPRODUCT Function in Excel?

This SUMPRODUCT is very simple easy to use. Let us now see how to use the SUMPRODUCT Function in Excel with the help of some examples.

You can download this SUMPRODUCT Function in Excel Template here – SUMPRODUCT Function in Excel Template

Example #1

In the below-mentioned example, suppose you have the following data range: the table contains Pharma products, their quantity & price.

SUMPRODUCT quantity & price

Here, Column D contains the Quantity of products in cells D8:D14; Column C contains Price in cells E8:E14; I need to find out the total sales. I need to multiply the quantity by price for each item and then add the SUMPRODUCT function is applied in an E15 cell to get total sales.

Select the cell E15 where the SUMPRODUCT function needs to be applied.

SUMPRODUCT function needs

Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “SUMPRODUCT” in the search for a function box, the SUMPRODUCT function will appear in select a function box. Double click on the SUMPRODUCT function.

SUMPRODUCT insert function

A dialog box appears where arguments (array1,[array2]) for SUMPRODUCT function needs to be filled or entered. i.e. =SUMPRODUCT(D8:D14,E8:E14)

SUMPRODUCT 9array)

Here, SUMPRODUCT internally has multiplied each element of both the range of cells or arrays and then added it to one another.

i.e. =D8*E8 + D9*E9 + D9*E9 + D10*E10 + D11*E11 + D12*E12 + D13*E13 + D14*E14

Here, SUMPRODUCT returns the output or result value, i.e. 42873

result value

Example #2

SUMPRODUCT can be used with specific user-defined mathematical operators, instead of default multiplication operation, E.G. +, -, X, & /

Just comma is replaced in the SUMPRODUCT function with specific mathematical operators.

mathematical operators

I have a data range in the above table where the table contains January & February month Sales of products. I need to find out the total sale; SUMPRODUCT the T function is applied in cell I15

find out the total sales

i.e. =SUMPRODUCT(H9:H13+I9:I13) Here Just comma is replaced with mathematical operators, i.e. addition symbol (+)

Addition

Here, SUMPRODUCT internally has added each element of both the range of cells or arrays. It returns the output value as 1417, i.e. Total sales of both months.

sales of both months

Example #3

Let’s look out how the SUMPRODUCT function works with the conditional operator “=” equals to. In the below-mentioned example, the Data table (N9:P16) contains a region in column O & Subscribers in column P

Subscribers in column

Here Using the SUMPRODUCT function, I need to find out the total Subscribers in a south region only.

i.e. =SUMPRODUCT((O9:O16=”South”)*(P9:P16))Here in the first array. In region range, we need to add conditional operator “=” equals to, i.e. = “South” because we need to find out, total Subscribers in the south region

 Subscribers in the south region

The subscribers of the south region get added up as a result of this conditional operator.

SUMPRODUCT search the region “South” in the defined range & will only do the SUM for the values of ‘south’ region only & It returns the output value as 3541, i.e. Total number of subscribers in the south region

SUMPRODUCT Example 3.2

Example #4

For brand names in column B, I need to find out the total characters in a specific range.

SUMPRODUCT Example 4

Prior to the SUBPRODUCT function, let’s check how the Len function works

LEN function returns the length of the specified string. The syntax for LEN function: =LEN(text) In the below mention example, I need to find out the length of string for the word “ITECH ANALYTICS”, Here the LEN function is applied in cell “G21” by using formula =LEN(F21).

SUMPRODUCT Example 4.1

It will return an output value as 15

SUMPRODUCT Example 4.2

LEN function integrated with the SUMPRODUCT function

Now, let’s combine SUBPRODUCT FUNCTION along with LEN; it calculates the length of all the characters in the data range or array (Brand name) by cell wise and then adds them all

SUMPRODUCT Example 4.3

It returns the output value as 43.

SUMPRODUCT Example 4.4

Example #5

In this example. Let’s imagine a case where I have data of inventory of products at a shoe shop. The cost of various products such as red tape, Reebok, Nike, Adidas, Fila, & other brands, with their corresponding quantity, is available in the data range. Here I need to calculate the average cost of a product in my shop. Column H contains price in cells H23:H32, and Column I contain quantity in cells I23:I32.

SUMPRODUCT Example 5

The formula OR syntax to calculate the required weighted average will be

=SUMPRODUCT (price, quantities) / SUM(quantities)

i.e. =SUMPRODUCT(H23:H32, I23:I32)/SUM(I23:I32)

SUMPRODUCT Example 5.1

The OUTPUT value or result will give the average cost of all the shoe products in that shop is

SUMPRODUCT Example 5.2

Things to Remember

  • All ranges or arrays must have the same number of rows and columns.
  • If there is no numeric value in the range or array, these values will be treated as 0.
  • If the range or arrays in the formula should have the same number of rows and columns, otherwise, it will return #VALUE! Error.
  • Sumproduct does not allow wildcard characters; wildcard characters (?, * ) cannot be used in the SUMPRODUCT Function in Excel.

Recommended Articles

This has been a guide to SUMPRODUCT Function in Excel. Here we discuss the SUMPRODUCT Formula in excel and how to use SUMPRODUCT Function in Excel along with an excel example and downloadable excel templates. You may also look at these useful functions in excel –

  1. SUMIF Formula in Excel
  2. Excel SUMIF with OR
  3. SUM Cells in Excel
  4. PRODUCT Function in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download SUMPRODUCT Function in Excel Template

EDUCBA

Download SUMPRODUCT Function in Excel Template

EDUCBA

डाउनलोड SUMPRODUCT Function in Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more