Other Links

This is one of the 30 lessons from the
Tutorial on Excel Formulas

Lesson 3-7: Excel SUMPRODUCT
(Level: Intermediate and Advanced)

Microsoft announces the arrival of an extraordinary new function in Excel 2007.They say that SUMIFS (with an "S") will revolutionize the reporting practice allowing users to automate most of their reports without any macros or programming....but the revolution has already begun.

In fact in earlier versions of Excel (since 1997) there is a function that does what SUMIFS will do and even more. It is called SUMPRODUCT. Microsoft has just neglected to tell the users.

IMPORTANT NOTE: To achieve what is shown below with SUMPRODUCT you cannot use the "Insert Function" dialog window:

You will rarely have a need for what this window allows you to do with SUMPRODUCT which is multiply by each other many pairs of data and calculate a total. For example you will calculate the total sales from the table below:

the result being: $60.00

To do all the things that SUMPRODUCT can do and to become part of the revolution you need to type in the formula.

Example of Data, Report and Chart:

You get data from central databases, accounting, sales and manufacturing programs and you need to develop reports. See the lesson 1-8 on Excel databases and all the functionalities that become available when Excel recognize your set of data as a database (sorting, filtering, pivot tables, subtotals, outlines, form).

In your set of data there can be 500 lines, 65,000 lines and even 1,000,000 lines (Excel 2007). The order of the data may differ from time to time data can be sorted by date, by product, by city, etc.. There can be more columns on salesperson,, time, department, store, etc. SUMPRODUCT will handle it all.

** The totals include all the data in the database not just the data in the picture above.

You need a report and chart (Sales by City ($) as above) that feeds on the set of data. This report must be refreshed AUTOMATICALLY when you add data to the database, when you modify the data or when you copy/paste another set of data. There could also be other reports (Sales by Month ($), Sales by City (Quantity), Sales by Month and City ($), etc.) that feed on the same set of data and they need to be refreshed AUTOMATICALLY all together. Sometimes you need to sort the data or filter it and it should not change the reports.

To achieve such a miracle you will develop ONE SUMPRODUCT formula in the top/left cell of each table (Bikes/Montreal in the example above) and be able to copy/paste it all over the table. This formula shouldn't need to be modified whatever the dataset.

The SUMPRODUCT Formula

The basic syntax for a SUMPRODUCT formula is as follow:
=SUMPRODUCT((Condition 2)*(Condition 1)*(What to sum))

For the example above and if the data and the report are on the same sheet the formula would look like this:
=SUMPRODUCT((B2:B500="Bikes")*(C2:C500="Montreal")*(E2:E500))
In plain English: Sum cells E2 to E500 if in cells C2 to C500 the value is "Montreal" and the value in cells B2 to B500 is "Bikes".

Now let's replace the conditional values by the address of the cells where they are:
=SUMPRODUCT((B2:B500=A13)*(C2:C500=B11)*(E2:E500))

Because the data is on a sheet named "Data" and the report is on a sheet named "Report" the formula looks like this:
=SUMPRODUCT((Data!B2:B500=A13)*(Data!C2:C500=B11)*(Data!E2:E500))

Finally because we want to copy/pate the formula all over the report we need to add dollar signs ($) to the different addresses:
=SUMPRODUCT((Data1!$B$2:$B$500=$A13)*(Data1!$C$2:$C$500=B$11)*(Data1!$E$2:$E$500))
Note: To add dollar signs to the addresses click on the address in the formula bar just above the grid and use the F4 key one, two, three or four times. See lesson 3-4 for more details.

Supplementary Tutorial and Examples

You can design 20 different reports looking at the same set of data (by city, by month, by product or any other combination) and when you add new data or when you replace the set of data (copy/paste) the reports are automatically refreshed.

Learn much more on SUMPRODUCT in a special extensive tutorial that is part of 25 Excel Spreadsheets that will change the way you work with Excel. Whatever your knowledge of Excel you will benefit from these spreadsheets.

Open Excel and open the following spreadsheets from the directory where you have downloaded the tutorial on Excel:

With SUMPRODUCT you can sum, calculate averages, use one or ten conditions, use any number of values per condition. You can use SUMPRODUCT with other functions and use SUMPRDUCT to do things that VLOOKUP cannot do. See all these with step by step detailed instructions in: "excel-tutorial-sumproduct.xls"

 You need to develop a permanent inventory. When you enter the in's and the out's you want your inventory to be updated AUTOMATICALLY. If the stock of a certain item is low you want Excel to tell you. No need for complex macros just use SUMPRODUCT. Learn how to do this with detailed explanations in  "excel-template-inventory.xls"

Using the Excel drop-down lists (lesson 2-2) you can build a single report template and just select a branch,  department, a salesperson...among 50 and the report is AUTOMATICALLY refreshed. No more spreadsheets with 50 templates to maintain. Learn how to do this with detailed explanations in "excel-example-dynamic-report.xls"