=SUMPRODUCT(array1, [array2], [array3], ...)One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward.
Need more than a single SUMPRODUCT formula? Try our free AI for Excel — it reads your entire spreadsheet, answers questions in plain English, and builds charts and analysis in the same chat.
array1:
The first array or range of cells that you want to multiply and then sum.
array2:
The second array or range of cells that you want to multiply with array1. This parameter is optional.
array3:
The third array or range of cells that you want to multiply with the previous arrays. This parameter is optional, and you can include additional arrays as needed.
The SUMPRODUCT function in Excel is a versatile tool that allows you to multiply corresponding components in given arrays and then sum those products. It can be used for a variety of calculations, including conditional sums, making it a powerful function for data analysis.
In simpler terms, the SUMPRODUCT function helps you perform calculations across multiple ranges, enabling you to analyze data more effectively.
Imagine you are managing a sales report for a small business, and you have the following data in an Excel spreadsheet:
| Salesperson | Sales Amount | Quantity Sold |
|---|---|---|
| Alice | 200 | 3 |
| Bob | 150 | 2 |
| Charlie | 300 | 5 |
| David | 250 | 4 |
| Eve | 100 | 1 |
You want to find the total revenue generated by each salesperson, which is calculated by multiplying the sales amount by the quantity sold.
To calculate the total revenue, you would use the SUMPRODUCT formula as follows:
=SUMPRODUCT(B2:B6, C2:C6)When you apply the formula, it multiplies each sales amount by the corresponding quantity sold and then sums the results. The output would be:
| Total Revenue |
|---|
| 1850 |
Here, the total revenue is calculated as (200 * 3) + (150 * 2) + (300 * 5) + (250 * 4) + (100 * 1) = 600 + 300 + 1500 + 1000 + 100 = 1850.
The SUMPRODUCT function is particularly useful when you need to perform calculations that involve multiple criteria or ranges without having to create additional columns for intermediate calculations. It allows you to streamline your data analysis and derive insights efficiently.
Understanding how to use the SUMPRODUCT function can significantly enhance your data analysis capabilities in Excel, enabling you to extract meaningful insights from your datasets.
Happy calculating!
You have a table where Column A contains product categories, Column B contains the corresponding sales figures, and Column C contains the number of units sold for each product. You can use the SUMPRODUCT function to calculate the total revenue generated by each product category by multiplying the sales figures by the number of units sold and summing the results. This allows for a comprehensive analysis of product performance and helps in inventory management and sales forecasting without the need for complex calculations.
Beyond the SUMPRODUCT function: Better Analyst is a full Excel AI that goes well past individual formulas. Upload any spreadsheet and ask questions in plain English — you'll get formulas, data analysis, and charts back in seconds. It's a free AI Excel assistant that works with any .xlsx, .xls, or .csv file — no plugins, no Microsoft 365 required.
Don't have a spreadsheet yet? Use our free AI spreadsheet generator to turn a text prompt into a downloadable Excel file — a full AI Excel sheet maker that builds headers, columns, and formulas from your description.
Create a free account for formula help, explanations, and spreadsheet analysis. Usage limits depend on your plan.
Browse our complete library of Excel formula guides
Compare your options
Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose.
AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.