Excel Lesson 17 of 30: Excel SUMPRODUCT

Microsoft's Best Kept Secret

If you call the SUMPRODUCT function from the function icon Microsoft tells you that SUMPRODUCT...

I have been using Excel for 20 years and I have been a full time Consultant in Excel for 12 years yet I have used SUMPRODUCT to perform this kind of calculation only 2 or 3 times.

But I use SUMPRODUCT daily to solve all kinds of business data problems. It is the most powerful and simple function in Excel. With SUMPRODUCT you can AUTOMATE most of your reports based on sets of data that you download from all kinds of databases and large programs. With SUMPRODUCT you can develop very rapidly and very simply permanent inventories, management  applications by project, by department, by piece of equipment, by product.....time management applications and so on.

If somebody tells you that he is an expert in Excel and he doesn't start by sharing his knowledge of SUMPRODUCT....beware.

Here is an example of what can be done with SUMPRODUCT

Take a few minutes and try this short exercise. If you don't discover how important the SUMPRODUCT function is...I apologize.

Here is a typical set of sales data that starts on row 1. But imagine a set of accounting data, manufacturing data, planning data....there can be 65,000 lines and 256 columns in the table and the order of the data in not important, SUMPRODUCT will do the job.

Below is a report designed using the SUMPRODUCT function.

In cell B2 of your report copy/paste from this webpage the following formula:

=SUMPRODUCT(($G$2:$G$20=$A2)*($H$2:$H$20=B$1)*($I$2:$I$20))

In plain English this formula says: sum the numbers in the range I2 to I20 if in the range H2 to H20 there is the same value as in cell B1 (New York) and if in the range G2 to G20 there is the same value as in cell A2 (Bicycles).

Use the "Copy/Paste Special/Formula" to enter the same formula in cells B2 to D4.

1- In cells G11:I11 add Bicycles, New York and 9999. See the report AUTOMATICALLY updated.

2- In cells G12:I12 add Skis, Houston and 9999. See the report AUTOMATICALLY updated.

3- Select cell G2 and sort the data using the icon  . See that the report doesn't change.

4- Select cell H2 and sort the data using the icon  . See that the report doesn't change.

This ends the small exercise.

The general syntax for the SUMPRODUCT formula is as follows:

=SUMPRODUCT((criteria1)*(criteria2)*(what to sum))

There can be any number of criteria separated by asterisks and the order is not important. The last argument (what to sum) is always the range that needs to be summed.

With SUMPRODUCT you can design 5 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.

More, more...Each criteria can take one or more values, you can also count the number of lines using many criteria, you can use SUMPRODUCT as a "lookup" function, you can calculate averages......

When you start using SUMPRODUCT, you use it every day.

I sell a single product on this website for $49 all the rest is FREE. The product is a set of 30 spreadsheets including one that shows you many other things that you can do with SUMPRODUCT. Tens of formulas explained step by step. This single spreadsheet is worth $49 so you get 29 other interesting spreadsheets for FREE.

A new life starts when you discover SUMPRODUCT